converting nested list into dataframe/datatable

Question

I'm stuck with a pretty particular issue. I have a database with a JSON field:

# A tibble: 1 x 3
   field1 field2        JSONfield
    <int> <chr>         <chr>                                                      
1      43 stringgg      "{\"typ\": \"Liste mit Punkten\", \"min~

Now, if I applied the following pattern:

dbGetQuery(pool, mydatabase) %>% 
  mutate(json = map(JSONfield, ~ fromJSON(.) %>% 
                      as.data.frame(stringsAsFactors = FALSE))) %>% 
  unnest(json)

I would receive:

# A tibble: 2 x 10
   field1 field2     JSONfield                typ   min   max   items.1 items.2  items.3
    <int> <chr>       <chr>                   <chr> <int> <int> <fct>   <fct>    <fct>  
1     43  stringgg    "{\"typ\": \"Liste mit~ List~ 0     1      first  second   third   
2     43  stringgg    "{\"typ\": \"Liste mit~ List~ 0     1      3       0        7

Though the Output I desire is:

# A tibble: 2 x 10
   field1 field2     JSONfield                typ   min   max   items
    <int> <chr>       <chr>                   <chr> <int> <int> <list>  
1     43  stringgg    "{\"typ\": \"Liste mit~ List~ 0     1      <data.frame~

The JSON object looks like this:

{"typ": "Liste mit Punkten",  
   "min": 0, 
   "max": 1, 
   "items": [["first", "second", "third"], 
             [3, 0, 7]]}

Also the JSON objects that I have to deal with have a subset of up to 7 name/value pairs that may or may not occur in an object, therefore I'm looking for a rather unspecific solution.

I'm greatful for any help on this issue.

data.frame or data.table in R cannot accept another data.frame, data.table as colume. Either you save then as nested list, or concatenate items into one string column. — Sixiang.Hu
– Sixiang.Hu, Commented Sep 3, 2018 at 20:24

Weihuang Wong · Accepted Answer · 2018-09-03 21:14:41Z

Try

library(dplyr)
library(purrr)
library(tidyr)
library(jsonlite)

text <- '{"typ": "Liste mit Punkten",  
   "min": 0, 
   "max": 1, 
   "items": [["first", "second", "third"], 
             [3, 0, 7]]}'

dd <- data_frame(field1 = 43, field2 = "stringgg", JSONfield = text)

dd %>% 
  mutate(json = map(JSONfield, ~ fromJSON(.) %>%
      map_if(is.matrix, ~list(as.data.frame(.))) %>% as_tibble
  )) %>%
  unnest() %>%
  select(-JSONfield)
# # A tibble: 1 x 6
#   field1 field2   typ                 min   max items               
#    <dbl> <chr>    <chr>             <int> <int> <list>              
# 1     43 stringgg Liste mit Punkten     0     1 <data.frame [2 × 3]>

To see what's going on, we can inspect fromJSON(text):

# $typ
# [1] "Liste mit Punkten"

# $min
# [1] 0

# $max
# [1] 1

# $items
#      [,1]    [,2]     [,3]   
# [1,] "first" "second" "third"
# [2,] "3"     "0"      "7"

We convert the items element, which is a matrix, into a dataframe, and then put that into a list. Finally, we convert the entire list (containing typ, min, max, and items) into a tibble.

Collectives™ on Stack Overflow

converting nested list into dataframe/datatable

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related