How to convert a regular dataframe into JSON?

Question

I've seen different conversions done on Stack, and none of them have the results I need. I have a data frame that I imported from an Excel file, manipulated, and want to export as a JSON file. I have tried this:

    exportJson <- toJSON(data)
    print(exportJson)
    write(exportJson, "test.json")
    json_data <- fromJSON(file="test.json")

My data looks like this:

     Jill Jimmie Alex Jane 
Jill Jill    0   Jill  Jill  
Jimmie 0  Jimmie Jimmie 0   
Alex   0   Alex  Alex   0   
Jane Jane  Jane  Jane   0

My output looks like this:

     {
"Jill": ["Jill",
"0",
"0",
"Jane",
"0",
"0",
"0",
"0",
"0",
"0",
    ...

when I need it to look like this format:

    {
"nodes": [ 
    { 
      "id": "id1",
      "name": "Jill",
      "val": 1 
    },
    { 
      "id": "id2",
      "name": "Jill",
      "val": 10 
    },
    (...)
],
"links": [
    {
        "source": "id1",
        "target": "id2"
    },
    (...)
]
    }

I've seen ways of converting JSON to a dataframe and I am aware of RJSONIO, jsonlite, rjson, etc. , I've googled it, and maybe I am just missing an obvious answer.

Have you tried ***[googling](https://www.google.com/search?q=convert+dataframe+to+json+in+r&rlz=1C1FLDB_enUS727US727&oq=convert+dataframe+to+j&aqs=chrome.2.69i57j0l5.5544j0j7&sourceid=chrome&ie=UTF-8)*** your question? Also, Please read [How to make a great reproducible example in R?](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) — M--, Jul 21 '17 at 17:19
The jq utility is good at reformatting json. It looks like there is an R package for it: https://cran.r-project.org/web/packages/jqr/jqr.pdf — Damian, Jul 21 '17 at 17:20
Could you provide a sample of your dataframe? `dput(head(data))` — Matt Jewett, Jul 21 '17 at 17:22
Yes,I have Masoud. @MattJewett I included a sample dataset in my question edit. — sgatta, Jul 21 '17 at 17:58
It seems you may need to reformat your dataframe a bit before attempting to convert it over to JSON. Your desired JSON output has slots for `id`, `name`, and `val`, but those values don't exist in your dataframe. I would think you would need to reformat your dataframe so that it is just three columns with the `id`, `name`, and `val` data that you want output to JSON. — Matt Jewett, Jul 21 '17 at 18:23

Damian · Accepted Answer · 2017-07-21T18:20:51.417

The '.' command in jq will reformat the JSON data. Using the jqr package:

library(jqr)

# Unformatted (no whitespace)
x <- '{"a":1,"b":2,"c":[1,2,3],"d":{"e":1,"f":2}}'  

jq(x, '.')

Output reformatted (with whitespace)

{
    "a": 1,
    "b": 2,
    "c": [
        1,
        2,
        3
    ],
    "d": {
        "e": 1,
        "f": 2
    }
}

jq is also a available as a standalone utility: https://stedolan.github.io/jq/

How to convert a regular dataframe into JSON?

1 Answers1