CODEWITHSUNDEEP
rjson
Maria Lenarcik
Maria Lenarcik

Reputation: 31

Convert complex json to dataset

I have a problem with converting complex JSON to Dataset such as:

[{
    "dateTime": 1556368322971,
    "eventName": "sampleEvent",
    "vars": [{
        "distance": 350,
        "fuel" : 300,
        "vehicleType": null
    }]
}, {
    "dateTime": 1556368322971,
    "eventName": "sampleEvent",
    "vars": [{
        "distance": 360,
        "fuel" : 290,
        "vehicleType": "bus"
    }]
}]

I was already trying do this with method:

result <- fromJSON(file = "./all.json")


json_file <- lapply(result, function(x) {
  x[sapply(x, is.null)] <- NA
  unlist(x)
})

But the problem is that all NULL values are missing. This is very problematic, beause when I am converting it to dataSet(matrix) all values are completely in mess and there are no null values which is incorrect.

I expect to have one dataSet which should looks like:

dateTime      | eventName   | distance | fuel | vehicleType
1556368322971 | sampleEvent | 350      | 300  | NA
1556368322971 | sampleEvent | 360      | 290  | bus

Upvotes: 2

Views: 89

Answers (3)

DeduciveR
DeduciveR

Reputation: 1702

You can also use the map family from purrr and chain together flattening one level at a time. Just paying attention to the conflict between purrr and jsonlite for flatten.

library(purrr)

# This is how I imported your example JSON
result <- fromJSON("~/Desktop/test.JSON", simplifyVector = F)

# Note that I set simplifyVector = F so that a list is returned

result2 <- result %>%
  map(purrr::flatten) %>% 
  map(purrr::flatten) %>% 
  map_dfr(purrr::flatten)

print(result2)
# A tibble: 2 x 5
       dateTime eventName   distance  fuel vehicleType
          <dbl> <chr>          <int> <int> <chr>      
1 1556368322971 sampleEvent      350   300 NA         
2 1556368322971 sampleEvent      360   290 bus

Upvotes: 3

akrun
akrun

Reputation: 887531

An option with base R would be

cbind(result[1:2], do.call(rbind, result$vars))
#     dateTime   eventName distance fuel vehicleType
#1 1.556368e+12 sampleEvent      350  300        <NA>
#2 1.556368e+12 sampleEvent      360  290         bus

Upvotes: 3

Maurits Evers
Maurits Evers

Reputation: 50718

We can use tidyr::unnest 

library(tidyr)
result %>% unnest()
#      dateTime   eventName distance fuel vehicleType
#1 1.556368e+12 sampleEvent      350  300        <NA>
#2 1.556368e+12 sampleEvent      360  290         bus

Sample data

library(jsonlite)
result <- fromJSON("all.json")

where file all.json contains

[{
    "dateTime": 1556368322971,
    "eventName": "sampleEvent",
    "vars": [{
        "distance": 350,
        "fuel" : 300,
        "vehicleType": null
    }]
}, {
    "dateTime": 1556368322971,
    "eventName": "sampleEvent",
    "vars": [{
        "distance": 360,
        "fuel" : 290,
        "vehicleType": "bus"
    }]
}]

Upvotes: 4

Related Questions