Googme Googme - 1 year ago 85
JSON Question

How to extract strings from rows which have .json like format?

I have imported a

file using

However, one of the columns still looks as a
Im not really sure how proceed in order to extact the columns
from the

My example:

date <- as.Date(as.character( c("2015-02-13",
ID <- c(1,2,3)
name <- c("John","Michael","Thomas")
drinks <- c("Beer","Coffee","Tee")
consumed <- c(2,5,3)
john<- "{\"employeID\":\"1\",\"other_details\":{\"email\":\"\"},\"computer\":\"yes\"}"
michael<- "{\"employeID\":\"2\",\"other_details\":{\"email\":\"\"},\"computer\":\"yes\"}"
thomas<- "{\"employeID\":\"3\",\"other_details\":{\"email\":\"\"},\"computer\":\"yes\"}"
json <- c(john,michael,thomas)
df <- data.frame(date,ID,name,drinks,consumed,json)

Where the data.frame looks like that:

enter image description here

I would like to get the following format:

date ID name drinks consumed email computer
#1 2015-02-13 1 John Beer 2 yes
#2 2015-02-14 2 Michael Coffee 5 no
#3 2015-02-14 3 Thomas Tee 3 yes

What I have tried was to was first to use the
again in different variations but it always results in:


Error: Argument 'txt' must be a JSON string, URL or file.

How can I extract these fields properly?

Answer Source

First, try:


The ndjson package is faster than jsonlite and was built for flattening (it's very task-specific and not as swiss-army-knife-ish as the highly useful jsonlite pkg).

Or, we can keep the tidyverse idioms all the way through:


map_df(df$json, ~jsonlite::fromJSON(as.character(.))) %>% 
  bind_cols(select(df, -json)) %>% 
  mutate_if(is.factor, as.character) %>% 
  mutate_if(is.list, as.character) %>% 
  select(ID, name, drinks, consumed, everything())
## # A tibble: 3 × 8
##      ID    name drinks consumed computer employeID       date
##   <dbl>   <chr>  <chr>    <dbl>    <chr>     <chr>               <chr>     <date>
## 1     1    John   Beer        2      yes         1 2015-02-13
## 2     2 Michael Coffee        5      yes         2 2015-02-14
## 3     3  Thomas    Tee        3      yes         3 2015-02-14

And, you get your character columns.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download