Trying to turn a data frame into hierarchical json array using jsonlite in r

Question

I'm trying to get my super-simple data frame into something a little more useful - a json array in this case. My data looks like

| V1        | V2        | V3        | V4        | V5        |
|-----------|-----------|-----------|-----------|-----------|
| 717374788 | 694405490 | 606978836 | 578345907 | 555450273 |
| 429700970 | 420694891 | 420694211 | 420792447 | 420670045 |

and I want it to look like

[
{
    "V1": {
        "id": 717374788
    },
    "results": [
        {
            "id": 694405490
        },
        {
            "id": 606978836
        },
        {
            "id": 578345907
        },
        {
            "id": 555450273
        }
    ]
},
{
    "V1": {
        "id": 429700970
    },
    "results": [
        {
            "id": 420694891
        },
        {
            "id": 420694211
        },
        {
            "id": 420792447
        },
        {
            "id": 420670045
        }
    ]
}

]

Any thoughts on how I can make that happen? Thanks for your help!

possible duplicate of [Strategies for formatting JSON output from R](http://stackoverflow.com/questions/8288925/strategies-for-formatting-json-output-from-r) — , Jul 02 '14 at 07:09
Why do you say "using jsonlite"? Why not just write a loop or two and `cat` the output? I suspect the code to mash it into the nested list structure required to produce that output with toJSON is as complex as the raw loop-n-print solution. — Spacedman, Jul 02 '14 at 08:40
Can you paste the output of dput on your data frame so we have something to work with? Also, work backwards. Take your desired JSON, read INTO R wth fromJSON, then that gives you the required data format to produce the JSON you want with toJSON after mashing your data frame around. — Spacedman, Jul 02 '14 at 08:53

digEmAll · Accepted Answer · 2014-07-04T06:13:24.710

Your data.frame cannot be directly written into that format. In order to get the desired json, firstly you need to turn your data.frame into this structure:

list(
     list(V1=list(id=<num>),
          results=list(
                       list(id=<num>),
                       list(id=<num>),
                       ...)),
     ...)

Here's a way to apply the transformation to your example data:

library(jsonlite)
# recreate your data.frame
DF <- 
data.frame(V1=c(717374788,429700970),
           V2=c(694405490, 420694891),
           V3=c(606978836,420694211),
           V4=c(578345907,420792447),
           V5=c(555450273,420670045))

# transform the data.frame into the described structure
idsIndexes <- which(names(DF) != 'V1')
a <- lapply(1:nrow(DF),FUN=function(i){ 
                             list(V1=list(id=DF[i,'V1']),
                                  results=lapply(idsIndexes,
                                                FUN=function(j)list(id=DF[i,j])))
                           })

# serialize to json
txt <- toJSON(a)
# if you want, indent the json
txt <- prettify(txt)

Trying to turn a data frame into hierarchical json array using jsonlite in r

1 Answers1

Linked