Unnest json file with coordinates into dataframe in R

Question

I'm having trouble parsing a json file into a dataframe in R. I've been able to turn the json file into a data frame, but I can't seem to unnest the "geometry" column. Below is a sample of the json file

[
{
    "point_id": 4,
    "geometry": {
        "type": "Point",
        "coordinates": [
            -101.5961904,
            31.7070736
        ]
    },
    "NumericID": "4543842",
}
]

When I try to unnest using the code below, I get an error.

ex_data<-lapply(ex_data, function(x) ifelse (x == "NULL", NA, x))
ex_data<-as.data.frame(do.call(rbind, ex_data))
ex_data<-ex_data%>% bind_rows(ex_data) %>%    # make larger sample data
mutate_if(is.list, simplify_all)   # flatten each list element internally 
ex_data%>%unnest(geometry)->ex_data_unnest
Error: Each column must either be a list of vectors or a list of data frames 
[geometry]

Thanks

tobiasegli_te · Accepted Answer · 2017-10-19T14:17:31.237

Use jsonlite::stream_in() to read the JSON-file (I copied your example in a JSON-file several times):

df <- stream_in(file("test.json"))

Edit:

Unnesting the geometry columns:

df <- as.data.frame(cbind(df[,-2],
    do.call(rbind,df$geometry$coordinates),     
    df$geometry$type),
    stringsAsFactors = F)
names(df)[3:5] <- c("geometry.coordinates1","geometry.coordinates2","geometry.type")

df
   point_id NumericID geometry.coordinates1 geometry.coordinates2 geometry.type
1         4   4543842             -101.5962              31.70707         Point
2         4   4543842             -101.5962              31.70707         Point

Now you can access the values from the data.frame

Unnest json file with coordinates into dataframe in R

1 Answers1