separating a column

tidyverse
#1

I have a tibble by a column that is the chr structure, this chr data is similar JSON. now I want to separate all these columns for any id.
example:

tb <- tibble("id" = c(1:4),
             "data" = c('{ "name" : "david", "family" : "json" }',
             '{ "code" : { "$numInt" : "111" }, "option" : "o_1", "optionType" : "o-2", "category" : "weekly", "title" : "t_1", "description" : "my_description" }',
             '{ "myToken" : null, "my_Id" : 1234, "p_Id" : { "$numInt" : "56789" }, "my_Type" : "fax" }',
             '{ "my_Id" : 456456, "p_Id" : { "$numInt" : "852852" }, "my_Type" : "ee" }'
))
#2

Hi @saso_008,
This link has some example code on how to handle data of this type:
https://stackoverflow.com/questions/41988928/how-to-parse-json-in-a-dataframe-column-using-r

1 Like
#3

hi @DavoWW
thanks. I use one of ways from the link you but when run it getting an error.
do you know where is my problem?

tb %>% 
    mutate(json = map(data, ~ fromJSON(.) %>% as.data.frame())) %>% 
    unnest(json)
 
Error: Problem with `mutate()` input `json`.
x arguments imply differing number of rows: 0, 1
ℹ Input `json` is `map(data, ~fromJSON(.) %>% as.data.frame())`.
#4 
tb <- tibble("id" = c(1:4),
             "data" = c('{ "name" : "david", "family" : "json" }',
                        '{ "code" : { "$numInt" : "111" }, "option" : "o_1", "optionType" : "o-2", "category" : "weekly", "title" : "t_1", "description" : "my_description" }',
                        '{ "myToken" : null, "my_Id" : 1234, "p_Id" : { "$numInt" : "56789" }, "my_Type" : "fax" }',
                        '{ "my_Id" : 456456, "p_Id" : { "$numInt" : "852852" }, "my_Type" : "ee" }'
             ))
library(jsonlite)
library(tidyverse)


tb$data <- map(tb$data,
    ~fromJSON(.) %>% map(~ifelse(is.null(.),
                                 NA,
                                 ifelse(is.list(.),
                                        unlist(.)
                                        ,.))) %>% as.data.frame %>% tibble)
                  

unnest(tb,data)
2 Likes
#5

thanks @nirgrahamuk
I have another id in tibble similar under code that runs it get the previous error.

tb <- tibble("id" = c(1:5),
             "data" = c('{ "name" : "david", "family" : "json" }',
                        '{ "code" : { "$numInt" : "111" }, "option" : "o_1", "optionType" : "o-2", "category" : "weekly", "title" : "t_1", "description" : "my_description" }',
                        '{ "myToken" : null, "my_Id" : 1234, "p_Id" : { "$numInt" : "56789" }, "my_Type" : "fax" }',
                        '{ "my_Id" : 456456, "p_Id" : { "$numInt" : "852852" }, "my_Type" : "ee" }',
                        '{ "_id" : { "$oid" : "dafd" }, "active" : true, "code" : "001", "createdAtTime" : { "$date" : { "$numLong" : "222" } }, "field" : "my_filed'
             ))
Error: parse error: premature EOF
                                       { "_id" : { "$oid" : "dafd" }, 
                     (right here) ------^
#6

do you want help in skipping such lines ?
you cant parse non JSON lines as JSON, as they are not JSON.
the curly braces dont match.

#7

i want to get data from this json file and my structure when get json file is similar this data.
your solution for tb[1:4] it work but for last id dont work

#8

Yes, because the last example is not a JSON.
You should count the open and closed curly brackets.

#9

Thanks @nirgrahamuk
my database saves this parameter like this and I must find the problem.

#10

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.