I have data in a JSON file that looks like this:
[{"Mordecai": {"Location": [{"lat": "-17.82772", "lon": "31.05337", "name": "Harare", "countryCode": "ZWE"}, {"lat": "-17.3", "lon": "31.33333", "name": "Bindura", "countryCode": "ZWE"}]}}, {"ClavinCliff": {"Location": [{"lat": "-19.0", "lon": "29.75", "name": "Republic of Zimbabwe", "countryCode": "ZW"}, {"lat": "-17.82772", "lon": "31.05337", "name": "Harare", "countryCode": "ZW"}, {"lat": "3.72112", "lon": "24.65263", "name": "Matapi", "countryCode": "CD"}, {"lat": "-29.0", "lon": "24.0", "name": "Republic of South Africa", "countryCode": "ZA"}, {"lat": "39.76", "lon": "-98.5", "name": "United States", "countryCode": "US"}]}}, {"Stanford": {"Location": ["Zimbabwe", "Harare", "Matapi", "Bindura", "South Africa", "United States"]}}]
I used jsonlite to read this into R, and I thought I set up a dataframe; however, the data is in lists within each location column.
library(jsonlite)
HR2 <- fromJSON("ZWE_2007_Human_Rights_Watch.json", flatten = TRUE)
colnames(HR2)
HR2[,c("Mordecai.Location")]
I am inexperienced in working with data frames in R. I would like the data to be in a dataframe with location type, lat, lon, name, country code as the columns for each observation row. This is the closest I have come to arranging the way I would like, except that all the data for the location type is in one observation. I need each lat/lon/name/country code as its own observation, plus a column identifying the location type (in this case Mordecai).
HR2.i <- lapply(HR2$Mordecai, function(x){ unlist(x)})
library(plyr)
HR2.m <- rbind.fill(lapply(HR2.i, function(x) do.call("data.frame", as.list(x))))
head(HR2.m)
lat1 lat2 lon1 lon2 name1 name2
1 -17.82772 -17.3 31.05337 31.33333 Harare Bindura
countryCode1 countryCode2
1 ZWE ZWE
I'm missing something but I don't know what. Any ideas are greatly appreciated!
