I need to create a JSON file that matches the schema at the end of the post in R. I need this to originate from a CSV file that I will generate and it can be crafted to whichever format will make it easiest to achieve the result. The CSV file will be dynamic in nature, in the below example, the only variables that will change per given file are:
My CSV in it's first iteration is formatted as such and it can have multiple rows:
day | cityName | Type | price | order |
---|---|---|---|---|
2019-03-14 | City1 | 1 | 5 | 50 |
2019-03-14 | City1 | 2 | 10 | 75 |
2019-03-14 | City2 | 1 | 10 | 50 |
2019-03-14 | City2 | 2 | 15 | 75 |
2019-03-14 | City2 | 3 | 20 | 100 |
{
"day": "2019-03-14",
"city": [
{
"cityName": "city1",
"FreightA": [
{
"Type": 1,
"FreightSegments": [
{
"price": 10,
"order": 50
}
]
"Type": 2,
"FreightSegments": [
{
"price": 10,
"order": 75
}
]
}
]
"cityName": "city2",
"FreightA": [
{
"Type": 1,
"FreightSegments": [
{
"price": 10,
"order": 50
}
]
"Type": 2,
"FreightSegments": [
{
"price": 15,
"order": 20
}
"Type": 3,
"FreightSegments": [
{
"price": 20,
"order": 100
}
]
]
}
]
}
]
}
I've gone through relevant JSON posts and a number of trial and errors and am not able to create it. Any guidance/tips/workflow recommendations would be greatly appreciated.
Thank you,
library(jsonlite)
library(tidyr)
nest(df, FreightSegments = c(price, order)) |>
nest(FreightA = c(Type, FreightSegments)) |>
nest(city = c(cityName, FreightA)) |>
toJSON(pretty = TRUE)
# [
# {
# "day": "2019-03-14",
# "city": [
# {
# "cityName": "City1",
# "FreightA": [
# {
# "Type": 1,
# "FreightSegments": [
# {
# "price": 5,
# "order": 50
# }
# ]
# },
# {
# "Type": 2,
# "FreightSegments": [
# {
# "price": 10,
# "order": 75
# }
# ]
# }
# ]
# },
# {
# "cityName": "City2",
# "FreightA": [
# {
# "Type": 1,
# "FreightSegments": [
# {
# "price": 10,
# "order": 50
# }
# ]
# },
# {
# "Type": 2,
# "FreightSegments": [
# {
# "price": 15,
# "order": 75
# }
# ]
# },
# {
# "Type": 3,
# "FreightSegments": [
# {
# "price": 20,
# "order": 100
# }
# ]
# }
# ]
# }
# ]
# }
# ]
Using this input:
df = read.table(text = 'day cityName Type price order
2019-03-14 City1 1 5 50
2019-03-14 City1 2 10 75
2019-03-14 City2 1 10 50
2019-03-14 City2 2 15 75
2019-03-14 City2 3 20 100', header =T)