convert json list to a data frame in R

user_1457389 0
Viewed 43

I have a json file as follows:

{
"1234":{"Messages":{"1":{"Content":["How are you","today"]},"2":{"Content":["I am great"]}}},
"2344":{"Messages":{"1":{"Content":["It's a plan"]}}}}

I am trying to convert this content to this data frame:

df <- data.frame(id=c(1234,2344), Content1=c("How are you today","It's a plan"), Content2=c("I am great", ""))

I have tried a few things with jsonlite and pluck but challenged over the iterative part of the code.

Any advice appreciated. Thank you.

r json
3 Answers

We could read the .json into a list with fromJSON and then get the 'Content' with rrapply and convert to a data.frame

library(jsonlite)
library(rrapply)
library(dplyr)
library(tidyr)
lst1 <- fromJSON("file1.json")
 rrapply(lst1, condition = function(x, .xname)
     .xname == 'Content', how = "melt") %>% 
   select(-L2) %>%
   unite(L4, L4, L3, sep = "") %>%
  unnest(value) %>%
  pivot_wider(names_from = L4, values_from = value, values_fn = toString)

-output

# A tibble: 2 × 3
  L1    Content1           Content2  
  <chr> <chr>              <chr>     
1 1234  How are you, today I am great
2 2344  It's a plan        <NA>
user_37322710

This is admittedly a hack:

data.table::rbindlist(
  lapply(rapply(L, function(z) paste(z, collapse = " "), how = "replace"), 
         function(z) as.data.frame(z$Messages)),
  fill = TRUE, idcol = "id")
#        id           Content  Content.1
#    <char>            <char>     <char>
# 1:   1234 How are you today I am great
# 2:   2344       It's a plan       <NA>

It also works with dplyr if you prefer:

dplyr::bind_rows(
  lapply(rapply(L, function(z) paste(z, collapse = " "), how = "replace"), 
         function(z) as.data.frame(z$Messages)),
  .id = "id")
user_33582720

Within tidyverse we could pivot_longer followed by unnest_wider:

library(tidyr)
library(tibble)
library(jsonlite)

jsontext |>
  fromJSON() |>
  as_tibble() |>
  pivot_longer(everything()) |>
  unnest_wider(value, transform = ~paste(unlist(.), collapse = " "), names_sep = "_")

Output

# A tibble: 2 × 3
  name  value_1           value_2     
  <chr> <chr>             <chr>       
1 1234  How are you today "I am great"
2 2344  Its a plan        ""
user_47864660
Related