Importing and Conditioning Non-Standard JSON Data in R

Importing/Conditioning a File with a “Kind” of JSON Structure in R

In this article, we will explore how to import and condition a file with a non-standard JSON structure in R. The file format is not properly formatted as JSON, but it still contains the same information that can be useful for analysis or further processing.

Understanding the File Format

The file contains multiple lines of data, each representing a row in a dataset. Each line has a similar structure, with some variables having identical names and values across all rows. The data is not well-formatted as JSON, but it still conveys the same information.

For example, one line might look like this:

{"datetime":"2015-07-08 09:10:00","subject":"MMM","sscore":"-0.2280","smean":"0.2593","svscore":"-0.2795","sdispersion":"0.375","svolume":"8","sbuzz":"0.6026","lastclose":"155.430000000","companyname":"3M Company"}

This line represents a single row in the dataset, with each variable having a corresponding value.

Reading the File

To import this file into R, we can use various packages that read JSON data. Some popular options include rjson, jsonlite, and RJSONIO.

For this example, let’s use jsonlite, which is a popular and easy-to-use package for reading and writing JSON data.

Installing the Package

Before we start, make sure you have installed the jsonlite package. If not, you can install it using the following command:

install.packages("jsonlite")

Reading the File Using jsonlite

To read the file into R, we need to first convert it into a JSON string format.

library(jsonlite)

# Create a vector of JSON strings
json.text <- '{"datetime":"2015-07-08 09:10:00","subject":"MMM","sscore":"-0.2280","smean":"0.2593","svscore":"-0.2795","sdispersion":"0.375","svolume":"8","sbuzz":"0.6026","lastclose":"155.430000000","companyname":"3M Company"},{"datetime":"2015-07-07 09:10:00","subject":"MMM","sscore":"0.2977","smean":"0.2713","svscore":"-0.7436","sdispersion":"0.400","svolume":"5","sbuzz":"0.4895","lastclose":"155.080000000","companyname":"3M Company"},{"datetime":"2015-07-06 09:10:00","subject":"MMM","sscore":"-1.0057","smean":"0.2579","svscore":"-1.3796","sdispersion":"1.000","svolume":"1","sbuzz":"0.4531","lastclose":"155.380000000","companyname":"3M Company"}'

# Read the JSON string into a data frame
x <- fromJSON(paste0('[', json.text, ']'))

This code creates a vector of JSON strings and then uses fromJSON to convert it into a data frame.

Cleaning and Conditioning the Data

Now that we have imported the file into R, let’s clean and condition the data by removing unnecessary characters and converting the variables to appropriate types.

# Remove text from subject variable
x$subject <- gsub("subject:", "", x$subject)

# Convert datetime variable to Date format
library(lubridate)
x$date <- ymd(x$datetime)

# Remove text from sscore, smean, svscore, and sbuzz variables
x$sscore <- gsub("sscore:", "", x$sscore)
x$smean <- gsub("smean:", "", x$smean)
x$svscore <- gsub("svscore:", "", x$svscore)
x$sbuzz <- gsub("sbuzz:", "", x$sbuzz)

# Remove text from lastclose variable
x$lastclose <- as.numeric(x$lastclose)

# Convert svolume variable to numeric type
x$svolume <- as.numeric(x$svolume)

This code cleans and conditions the data by removing unnecessary characters, converting variables to appropriate types, and renaming some of the columns.

Creating a Final Data Frame

Now that we have cleaned and conditioned the data, let’s create a final data frame with all the necessary information.

# Create a final data frame with all the necessary information
final_data <- x[, c("date", "subject", "sscore", "smean", "svscore", "sdispersion", 
                     "svolume", "sbuzz", "lastclose", "companyname")]

This code creates a final data frame with all the necessary columns.

Conclusion

In this article, we explored how to import and condition a file with a non-standard JSON structure in R. We used various packages such as rjson, jsonlite, and RJSONIO to read the file into R. After importing the file, we cleaned and conditioned the data by removing unnecessary characters and converting variables to appropriate types. Finally, we created a final data frame with all the necessary information.

Last modified on 2023-05-15