Working with JSON Files in R Programming

JSON stands for JavaScript Object Notation. These files contain the data in human readable format, i.e. as text. Like any other file, one can read as well as write into the JSON files. In order to work with JSON files in R, one needs to install the “rjson”  package. The most common tasks done using JSON files under rjson packages are as follows:

• Install and load the rjson package in R console
• Create a JSON file
• Reading data from JSON file
• Write into JSON file
• Converting the JSON data into Dataframes
• Working with URLs 

Install and load the rjson package

One can install the rjson from the R console using the install.packages() command in the following way:

install.packages("rjson")

After installing rjson package one has to load the package using the library() function as follows:

library("rjson")

Creating a JSON file 

To create a JSON file, one can do the following steps:

• Copy the data given below into a notepad file or any text editor file. One can also create his own data as per the given format.

  { 
     "ID":["1","2","3","4","5"],
     "Name":["Mithuna","Tanushree","Parnasha","Arjun","Pankaj"],
     "Salary":["722.5","815.2","1611","2829","843.25"],
     "StartDate":["6/17/2014","1/1/2012","11/15/2014","9/23/2013","5/21/2013"],
     "Dept":["IT","IT","HR","Operations","Finance"]
  }

• Choose “all types” as the file type and save the file with .json extension.(Example: example.json)
• One must make sure that the information or data is contained within a pair or curly braces { } .

Reading a JSON file

In R, reading a JSON file is quite a simple task. One can extract and read the data of a JSON file very efficiently using the fromJSON() function. The fromJSON() function takes the JSON file and returns the extracted data from the JSON file in the list format by default.

Example:

Suppose the above data is stored in a file named example.json in the E drive. To read the file we must write the following code.

# Read a JSON file

# Load the package required to read JSON files.
library("rjson")

# Give the input file name to the function.
result <- fromJSON(file = "E:\\example.json")

# Print the result.
print(result)

Output:

$ID
[1] "1" "2" "3" "4" "5"

$Name
[1] "Mithuna"   "Tanushree" "Parnasha"  "Arjun"     "Pankaj"

$Salary
[1] "722.5"  "815.2"  "1611"   "2829"   "843.25"

$StartDate
[1] "6/17/2014"  "1/1/2012"   "11/15/2014" "9/23/2013"  "5/21/2013"

$Dept
[1] "IT"         "IT"         "HR"         "Operations" "Finance"

Writing into a JSON file 

One need to create a JSON Object using toJSON() function before he writes the data to a JSON file. To write into a JSON file use the write() function.

Example:

# Writing into JSON file.

# Load the package required to read JSON files.
library("rjson")

# creating the list
list1 <- vector(mode="list", length=2)
list1[[1]] <- c("sunflower", "guava", "hibiscus")
list1[[2]] <- c("flower", "fruit", "flower")

# creating the data for JSON file
jsonData <- toJSON(list1)

# writing into JSON file
write(jsonData, "result.json")

# Give the created file name to the function
result <- fromJSON(file = "result.json")

# Print the result
print(result)

Output:

[[1]]
[1] "sunflower" "guava"     "hibiscus"

[[2]]
[1] "flower" "fruit"  "flower"

Converting the JSON data into Dataframes

In R, to convert the data extracted from a JSON file into a data frame one can use the as.data.frame() function. 

Example:

# Convert the file into dataframe

# Load the package required to read JSON files.
library("rjson")

# Give the input file name to the function.
result <- fromJSON(file = "E://example.json")

# Convert JSON file to a data frame.
json_data_frame <- as.data.frame(result)

print(json_data_frame)

Output:

  ID      Name Salary  StartDate       Dept
1  1   Mithuna  722.5  6/17/2014         IT
2  2 Tanushree  815.2   1/1/2012         IT
3  3  Parnasha   1611 11/15/2014         HR
4  4     Arjun   2829  9/23/2013 Operations
5  5    Pankaj 843.25  5/21/2013    Finance

Working with URLs 

One can take datasets from any websites and extract the data and use them. This can be done under any of two packages, namely RJSONIO and jsonlite.

Example:

# working with URLs

# import required library
library(RJSONIO)

# extracting data from the website
Raw <- fromJSON(
       "https://data.ny.gov/api/views/9a8c-vfzj/rows.json?accessType=DOWNLOAD")

# extract the data node
food_market <- Raw[['data']]

# assembling the data into data frames
Names <- sapply(food_market, function(x) x[[14]])
head(Names)

Output:

[1] "LUCKY MART             " "CUMBERLAND FARMS 1587  "
[3] "K&M SPORTS             " "MASON&OLD RIDGE FARM   "
[5] "HAMPTON CHUTNEY CO     " "CM - HUTCHINSON        "