R - JSON Files

R - Working with JSON Files

JSON (JavaScript Object Notation) is a lightweight, text-based data-interchange format. It is human-readable and easy for machines to parse and generate. JSON is a primary format for data exchange on the web, commonly used in APIs and configuration files. In R, the jsonlite package is the modern standard for working with JSON, providing a robust and performant bridge between JSON data and R’s native data structures.

Best Practice: Using the jsonlite Package

While older packages like rjson exist, jsonlite is now the recommended choice. It is well-integrated with the modern R ecosystem, especially the tidyverse. It intelligently converts JSON objects into R data frames or lists. You can install it directly from CRAN. It’s also included if you install the tidyverse meta-package.

# Install the recommended package for JSON manipulation
install.packages("jsonlite")

# Or, install the entire tidyverse which includes jsonlite and other useful packages
# install.packages("tidyverse")

Input Data: A Modern JSON Example

Let’s create a well-structured JSON file. Note the use of proper data types: numbers for ID and Salary, and a standard YYYY-MM-DD format for dates. Save the following content in a file named employees.json.

{
    "ID": [1, 2, 3, 4, 5, 6, 7, 8],
    "Name": ["Rick", "Dan", "Michelle", "Ryan", "Gary", "Nina", "Simon", "Guru"],
    "Salary": [623.3, 515.2, 611.0, 729.0, 843.25, 578.0, 632.8, 722.5],
    "StartDate": ["2012-01-01", "2013-09-23", "2014-11-15", "2014-05-11", "2015-03-27", "2013-05-21", "2013-07-30", "2014-06-17"],
    "Dept": ["IT", "Operations", "IT", "HR", "Finance", "IT", "Operations", "Finance"]
}

Reading JSON and Converting to a Data Frame

The jsonlite::fromJSON() function can read a JSON file and simplify it into an R data frame in a single step, provided the JSON is structured as a collection of equal-length arrays, as in our example.

# Load the package
library(jsonlite)

# Define the file path (ensure 'employees.json' is in your working directory)
file_path <- "employees.json"

# Read the JSON file directly into a data frame
# The `fromJSON` function is smart enough to create a data frame here.
employees_df <- fromJSON(file_path)

# Print the resulting data frame
print(employees_df)

# Verify the structure to see the column types
str(employees_df)

When you execute the above code, you get a clean, ready-to-use data frame:

  ID     Name Salary  StartDate       Dept
1  1     Rick 623.30 2012-01-01         IT
2  2      Dan 515.20 2013-09-23 Operations
3  3 Michelle 611.00 2014-11-15         IT
4  4     Ryan 729.00 2014-05-11         HR
5  5     Gary 843.25 2015-03-27    Finance
6  6     Nina 578.00 2013-05-21         IT
7  7    Simon 632.80 2013-07-30 Operations
8  8     Guru 722.50 2014-06-17    Finance

'data.frame':  8 obs. of  5 variables:
 $ ID       : int  1 2 3 4 5 6 7 8
 $ Name     : chr  "Rick" "Dan" "Michelle" "Ryan" ...
 $ Salary   : num  623 515 611 729 843 ...
 $ StartDate: chr  "2012-01-01" "2013-09-23" "2014-11-15" "2014-05-11" ...
 $ Dept     : chr  "IT" "Operations" "IT" "HR" ...

Practical Application: Data Analysis with dplyr

Once the data is in a data frame, you can use powerful tools like dplyr for analysis. For instance, let’s convert the StartDate to a proper Date object and calculate the average salary per department.

# Load the dplyr package (part of the tidyverse)
library(dplyr)

# Perform data manipulation using pipes
employee_summary <- employees_df |>
  mutate(StartDate = as.Date(StartDate)) |>
  group_by(Dept) |>
  summarise(
    AverageSalary = mean(Salary),
    EmployeeCount = n()
  ) |>
  arrange(desc(AverageSalary))

# Print the summary table
print(employee_summary)

This modern, readable code produces a summary that is easy to interpret:

# A tibble: 4 × 3
  Dept       AverageSalary EmployeeCount
  <chr>              <dbl>         <int>
1 Finance             783.             2
2 HR                  729              1
3 IT                  597.             3
4 Operations          574.             2