[SOLVED] Extract data from json with lapply

Extract data from json with lapply

I have a long json file from which I want to extract the city name and its corresponding elevation. Result should be a list where column A holds the city name, column B the elevation.

Here some data for R:

l <- fromJSON('[{"_id": "CtNjHGG8asEvFyqsM","cronjobname": "Elev","cronjobid": "mmsibiZL4p42fL8jh",
"cronjobtyp": "importTestData","datasource": "importTestData","data": [{
 "Station": "New York","Elev":    300},{ "Station": "London","Elev":    35}],
"createdAt": "2016-04-30T22:10:11.342Z","createdBy": null,"modifiedAt": "2016-04-30T22:10:11.342Z","modifiedBy": null}]')

And that's what I came up with:

m <- lapply(
  l[[1]]$data, 
  function(x) c(x$Station['Station'], x$Elev['Elev'])
)
m <- do.call(rbind, m)

However, I know that this is not complete and should be l[[1]]$data[[1]]$Station to access the station, but I can't simply use [[x]]$Station unfortunately. What am I missing or do I need to put this into a loop to access multiple x?

Solution

Is that what you want?

  m<-lapply(
        l[[1]]$data, 
        function(x) c(x$Station, x$Elev)
  )
  m <- do.call(rbind, m)
  > m
  [,1]       [,2] 
  [1,] "New York" "300"
  [2,] "London"   "35"

Or you can use this:

  m <- do.call(rbind, l[[1]]$data)
  > m
  Station    Elev
  [1,] "New York" 300 
  [2,] "London"   35

The statement x$Station['Station'] equivalent to l[[1]]$data[[1]]$Station['Station'] so there is nothing in it.