JSON (JavaScript object notation) is a popular data format used for transmitting structured data. It is lightweight and commonly used in APIs and web services. In R, importing and cleaning JSON data is made simple with libraries like jsonlite and dplyr. This article will guide you through importing JSON data and cleaning it.
Installing Required Libraries
First, install the necessary libraries if you haven’t already. We’ll use jsonlite to parse JSON data and dplyr for cleaning.
install.packages("jsonlite")
install.packages("dplyr")
library(jsonlite)
library(dplyr)
Importing JSON Data
JSON data can be imported into R using the fromJSON() function. The data can come from a local file or a URL.
# Load necessary libraries
library(jsonlite)
library(dplyr)
# Sample JSON data (as given in your example)
json_data <- '[
{
"id": 101,
"name": "T-shirt",
"category": "Apparel",
"price": 20.0,
"stock": 50,
"available": true
},
{
"id": 102,
"name": "Jeans",
"category": "Apparel",
"price": null,
"stock": 30,
"available": true
},
{
"id": 103,
"name": "Sneakers",
"category": "Footwear",
"price": 60.0,
"stock": 20,
"available": true
},
{
"id": 104,
"name": "Hat",
"category": "Accessories",
"price": 15.0,
"stock": 50,
"available": true
},
{
"id": 105,
"name": "Jacket",
"category": "Apparel",
"price": 100.0,
"stock": 10,
"available": false
},
{
"id": 106,
"name": "Sweater",
"category": "Apparel",
"price": 45.0,
"stock": 40,
"available": null
}
]'
# Convert the JSON string to a data frame
df <- fromJSON(json_data)
# Print the imported data
print("Imported JSON data:")
print(df)
[[1]] [[1]]$ID [1] 101 [[1]]$Name [1] "T-shirt" [[1]]$Category [1] "Apparel" [[1]]$Price [1] 20 [[1]]$Stock [1] 50 [[1]]$Available [1] TRUE [[2]] [[2]]$ID [1] 102 [[2]]$Name [1] "Jeans" [[2]]$Category [1] "Apparel" [[2]]$Price NULL [[2]]$Stock [1] 30 [[2]]$Available [1] TRUE [[3]] [[3]]$ID [1] 103 [[3]]$Name [1] "Sneakers" [[3]]$Category [1] "Footwear" [[3]]$Price [1] 60 [[3]]$Stock [1] 20 [[3]]$Available [1] TRUE [[4]] [[4]]$ID [1] 104 [[4]]$Name [1] "Hat" [[4]]$Category [1] "Accessories" [[4]]$Price [1] 15 [[4]]$Stock [1] 50 [[4]]$Available [1] TRUE [[5]] [[5]]$ID [1] 105 [[5]]$Name [1] "Jacket" [[5]]$Category [1] "Apparel" [[5]]$Price [1] 100 [[5]]$Stock [1] 10 [[5]]$Available [1] FALSE [[6]] [[6]]$ID [1] 106 [[6]]$Name [1] "Sweater" [[6]]$Category [1] "Apparel" [[6]]$Price [1] 45 [[6]]$Stock [1] 40 [[6]]$Available NULL
Cleaning JSON Data
Once the JSON data is imported, you can clean it using dplyr functions.
Selecting Relevant Columns
If your JSON data contains several fields, you may want to select only the columns that are relevant to your analysis. This can be done with the select() function from dplyr.
# Select only 'id', 'name', and 'price df_cleaned % select(id, name, price)
Renaming Columns
It’s a good practice to rename columns to make them more readable and consistent. You can use the rename_all() function to rename all columns. For example, converting all column names to lowercase:
df_cleaned % rename_all(tolower) # Rename all columns to lowercase
Handling Missing Values
JSON data may contain missing values, which are typically represented as null in the JSON file. In R, null values are converted to NA. You can handle missing values by replacing them with a default value or removing them entirely.
In this example, we will replace NA in the price column with 0 and NA in the available column with FALSE.
df_cleaned %
mutate(
price = ifelse(is.na(price), 0, price), # Replace NA in 'price' with 0
available = ifelse(is.na(available), FALSE, available) # Replace NA in 'available' with FALSE
)
Filtering Rows
You can filter out rows that contain NA values or any other unwanted conditions. For example, if you want to filter out rows where price or stock is missing, you can use the filter() function.
df_cleaned % filter(!is.na(price) & !is.na(stock))
Sorting Rows
Sorting the data is a common operation to help organize and analyze the data. You can sort rows by any column using the arrange() function. For example, to sort the data by price in descending order, use:
df_cleaned % arrange(desc(price)) # Sort by 'price' in descending order
Saving the Cleaned Data
Once the data is cleaned and transformed, you can save it for future use. You can save it as a CSV file or a JSON file.
# Save the cleaned data as a CSV file write.csv(df_cleaned, "cleaned_data.csv", row.names = FALSE) # Save the cleaned data as a JSON file write_json(df_cleaned, "cleaned_data.json")
Conclusion
In this article, we learned how to import and clean JSON data in R using the jsonlite and dplyr packages. We covered how to import JSON data from files or URLs, choose the columns you need, handle missing values, and sort the data. Cleaning the data helps make it ready for analysis.
