Turn a json list to a data frame

Question

I have a json list that its saved as text, and i'm trying to convert it into a data frame.List looks like that:

`{"posts": {
        "data": [
          {
            "comments": {
              "data": [
                {
                  "created_time": "2020-01-25T16:48:03+0000",
                  "message": "I love all kind of art and paintings. However 19 thousand dollars for a painting is entirely too much!!. ",
                  "id": "1579832716452874_1373756966159579"
                },
                {
                  "created_time": "2020-01-25T15:21:29+0000",
                  "message": "The wind blows a piece of paper and lands next to your house. You unravel it, \"Hey hope you are having a nice day!\"...",
                  "id": "1579832716452874_1373704542831488"
                }
              ]
            }
          }
        ]
      }
    }`

and so on. I would like for my data frame to be split in the following columns:

`created_time|message|id`

along with their respective data. I have tried the following command but with no success as i'm getting the same exact output:

` df <- data.frame(matrix(unlist(data), ncol=length(data), byrow = FALSE))`

Also because the data are saved as text, the json packages (rjson,jsonlite) won't work. Any suggestions will be greatly appreciated.

What does "saved as text" mean? That sounds like a good thing. I'm not sure what JSON stored as something other than text would look like. — cory, Jan 29 '20 at 19:36
check [this](https://stackoverflow.com/questions/16947643/getting-imported-json-data-into-a-data-frame/37739735) thread. — eonurk, Jan 29 '20 at 19:38
So using data <- fromJSON(data) i get the following error:Error: Argument 'txt' must be a JSON string, URL or file. — Xristos Solwmou, Jan 29 '20 at 19:46
Can you complete your example data so that it's syntactically valid JSON to test on? Right now it's truncated, so it's not clear what the original structure is. — Brian, Jan 29 '20 at 23:15
@XristosSolwmou: I'm really trying to help you. However, is this really the correct JSON? As far as I get it there is one closing `]` missing and the entire string should be surrounded by `{ }`. — Georgery, Jan 30 '20 at 10:38
Hi @Gregory, sorry about that. They are a long of data and i am only including a small portion, hence the mistake when pasting the code. I have updated the post. — Xristos Solwmou, Jan 30 '20 at 11:42

score 0 · Accepted Answer · answered Jan 31 '20 at 18:51

The issue you seemed to be having was with the quotation marks. If you have your JSON object saved as a text file, {jsonlite} will automatically escape those characters when reading the file. The defaults for fromJSON flatten lists into dataframes when possible, which is what you wanted.

x <- jsonlite::fromJSON("complex_json.json")

x_df <- x$posts$data[1]$comments$data[[1]]

tibble::as_tibble(x_df)  # tibble is for pretty-printing purposes only

# A tibble: 2 x 3
  created_time        message                                                          id                   
  <chr>               <chr>                                                            <chr>                
1 2020-01-25T16:48:0~ "I love all kind of art and paintings. However 19 thousand doll~ 1579832716452874_137~
2 2020-01-25T15:21:2~ "The wind blows a piece of paper and lands next to your house. ~ 1579832716452874_137~

The only tricky bit is figuring out the x$posts$data[1]$comments$data[[1]]. I always do that interactively at the console, occasionally checking str() of the resulting object to see where there are hidden levels of nesting that aren't printing clearly.

Turn a json list to a data frame

1 Answers1