Convert JSON data to Dataframe in R

In the field of Data Analysis, we have to manage data in various formats, one of which is JSON (JavaScript Object Notation). JSON is used for storing and exchanging data between different systems and is hugely used in web development.

In R Programming language, we have to work often with data in different formats and convert it to a necessary format for analysis. In this case, it is necessary to convert the JSON data to a Dataframe, which is a common data structure in R. This language has several packages that make it easy to work with JSON data. 

Let’s take a JSON data named sample.json

[
  {
    "name": "Sowham",
    "age": 20,
    "city": "Kolkata"
  },
  {
    "name": "Kushal",
    "age": 19,
    "city": "Jalpaiguri"
  },
  {
    "name": "Rohan",
    "age": 21,
    "city": "Durgapur"
  }
]

Now, let us see how we can convert JSON data into a dataframe using various packages in R.

Convert JSON data to Dataframe using Jsonlite Package in R

The jsonlite package is used for interacting with SQLite databases using JSON data. It is used to create, query, insert, update, and delete data in an SQLite database using a flexible interface for handling complex data structures. 

Steps to convert JSON data to a Dataframe in R using the Jsonlite Package

Installing jsonlite package:

First, we have to install the “jsonlite” package by writing the following command:

install.packages("jsonlite")

Loading JSON data :

We load the data using the fromJSON() function from the jsonlite package in R language:

library(jsonlite)

sample_data <- fromJSON("sample.json")

Converting JSON data to Dataframe :

The fromJSON() function returns a list in R. We have to convert this list to a dataframe by using the as.data.frame() function :

output_dataframe <- as.data.frame(sample_data)

R

# First of all we have to Load jsonlite package
library(jsonlite)

# Then we have to load JSON data
sample_data <- fromJSON("sample.json")

# Then convert  the JSON data to dataframe
output_dataframe <- as.data.frame(sample_data)

# At last print the output dataframe
print(output_dataframe)

Output:

This output_dataframe  will have three columns named name, age, and city, and three rows with the data from the JSON file.

    name age       city
1 Sowham  20    Kolkata
2 Kushal  19 Jalpaiguri
3  Rohan  21   Durgapur

Convert JSON data to Dataframe using RJSONIO Package in R

The RJSONIO package is used for working with JSON data. It provides functions for converting between JSON data and R objects, as well as for parsing and generating JSON data. 

Steps to convert JSON data to a Dataframe in R using the RJSONIO Package

Installing the RJSONIO package:

First, we have to install the “RJSONIO” package by using the following command:

install.packages("RJSONIO")

Loading JSON Data:

We load the sample.json data  using the fromJSON function from the RJSONIO package in R language:

library(RJSONIO)

sample_data <- fromJSON("sample.json")

Converting JSON data to Dataframe :

We have to convert this list to a dataframe by using the as.data.frame function :

output_dataframe <- as.data.frame(sample_data)

R

# Load RJSONIO package
library(RJSONIO)

# Load JSON data
sample_data <- fromJSON("sample.json")

# Convert JSON data to dataframe
output_dataframe <- as.data.frame(sample_data)

# Print the output dataframe
print(output_dataframe)

Output:

    name age    city name.1 age.1     city.1 name.2 age.2   city.2
1 Sowham  20 Kolkata Kushal    19 Jalpaiguri  Rohan    21 Durgapur

Convert JSON data to Dataframe using Rjson Package in R

The rjson package provides functions for converting JSON data to lists, vectors, and data frames and vice versa, and also for parsing and generating JSON data. 

Steps to convert JSON data to a Dataframe in R using the Rjson Package

Installing rjson package:

install.packages("rjson")

Loading JSON Data:

sample_data <- fromJSON(file = "sample.json")

Converting JSON data to Dataframe :

output_dataframe<- data.frame(matrix(unlist(sample_data), 
ncol = length(sample_data[[1]]), byrow = TRUE), stringsAsFactors = FALSE)

R

# Load rjson package
library(rjson)

# Load JSON data
sample_data <- fromJSON(file = "sample.json")


# Convert JSON data to dataframe
output_dataframe <- data.frame(matrix(unlist(sample_data),
                        ncol = length(sample_data[[1]]), byrow = TRUE),
                        stringsAsFactors = FALSE)

# Print the output dataframe
print(output_dataframe)

Output:

This output_dataframe will have three columns named name, age, and city, and three rows with the data from the JSON file.

      X1 X2         X3
1 Sowham 20    Kolkata
2 Kushal 19 Jalpaiguri
3  Rohan 21   Durgapur

Convert JSON data to Dataframe using Tidyjson Package in R

The Tidyjson package along with dplyr package provides functions to convert JSON data into a dataframe in R. 

Steps to convert JSON data to a Dataframe in R using the Tidyjson Package:

Installing tidyjson package:

install.packages("tidyjson")

Loading JSON Data:

json_data <- jsonlite::fromJSON("sample.json")

Converting JSON data to Dataframe :

df <- as.data.frame(json_data)

R

# Step 1: Install and load the RJSONIO package
install.packages("tidyjson")

library(tidyjson)
library(dplyr)

# Step 2: Read the JSON data
json_data <- jsonlite::fromJSON("sample.json")

# Step 3: Convert the R object to a data frame
df <- as.data.frame(json_data)

# Step 4: View the data frame
head(df)

Output:

    name age       city
1 Sowham  20    Kolkata
2 Kushal  19 Jalpaiguri
3  Rohan  21   Durgapur