如何将JSON输出提取到DataFrame？

Question

我有一个包含3000多条记录的数据框架，其中包括每次观测的经纬度坐标。我想从每一组坐标中得到国家和州或省。

或者，一个更好的解决我的问题，获得空间信息是赞赏的！

下面是我的代码：

library(RDSTK)
library(httr)
library(rjson)
Coords <- structure(list(Latitude = c(43.30528, 46.08333, 32.58333, 46.25833, 45.75, 46.25, 45.58333, 45.58333, 44.08333, 45.75), 
                         Lontitude = c(-79.80306, -82.41667, -117.08333, -123.975, -85.75, -123.91667, -86.75, -86.75, -76.58333, -85.25
                                         )), .Names = c("Latitude", "Longitude"), row.names = c(1L, 2L, 3L, 4L, 5L, 6L, 7L, 8L,9L, 10L), class = "data.frame")

json_file <- fromJSON(coordinates2politics(Coords$Latitude, Coords$Longitude))

Answer 1

我更喜欢在R中使用jsonlite解析JSON。

要解析嵌套的JSON列表，可以在lapply中执行fromjson调用。

JSONLite::FromJSON试图简化结果。但是，由于JSON被设计为处理嵌套的数据结构，所以通常会返回一个列表的data.frame，因此要获得您要获取的data.frame，您需要知道您想要的列表中的哪个元素，并提取它。

例如

library(RDSTK)
library(jsonlite)

js <- coordinates2politics(Coords$Latitude, Coords$Longitude)
lst <- lapply(js, jsonlite::fromJSON)

lst[[1]]$politics
#           type friendly_type                       name  code
# 1       admin2       country                     Canada   can
# 2       admin4         state                    Ontario  ca08
# 3 constituency  constituency            Hamilton Centre 35031
# 4 constituency  constituency                 Burlington 35010
# 5 constituency  constituency Hamilton East-Stoney Creek 35032

要获取data.frame，可以构造另一个lapply来提取所需的元素，然后将其与do.call(...，rbind)或我的首选项与data.table::rbindlist(...)放在一起

lst_result <- lapply(lst, function(x){
    df <- x$politics[[1]]
    df$lat <- x$location$latitude
    df$lon <- x$location$longitude
    return(df)
})

data.table::rbindlist(lst_result)

#            type friendly_type                                  name                   code      lat        lon
# 1:       admin2       country                                Canada                    can 43.30528  -79.80306
# 2:       admin4         state                               Ontario                   ca08 43.30528  -79.80306
# 3: constituency  constituency                       Hamilton Centre                  35031 43.30528  -79.80306
# 4: constituency  constituency                            Burlington                  35010 43.30528  -79.80306
# 5: constituency  constituency            Hamilton East-Stoney Creek                  35032 43.30528  -79.80306
# 6:       admin2       country                                Canada                    can 46.08333  -82.41667
# 7:       admin4         state                               Ontario                   ca08 46.08333  -82.41667

或者，要获得关于每个LAT/LON的更多细节，您可以通过库(googleway)（免责声明：我写了googleway）使用Google的API，对LAT/LON进行反向地理编码。

为此，您需要一个有效的Google API密钥（除非您付费，否则每天仅限2500个请求）

library(googleway)

key <- "your_api_key"

lst <- apply(Coords, 1, function(x){
    google_reverse_geocode(location = c(x["Latitude"], x["Longitude"]),
                           key = key)
})

lst[[1]]$results$address_components
# [[1]]
#                              long_name                           short_name                                  types
# 1 Burlington Bay James N. Allan Skyway Burlington Bay James N. Allan Skyway                                  route
# 2                           Burlington                           Burlington                    locality, political
# 3         Halton Regional Municipality         Halton Regional Municipality administrative_area_level_2, political
# 4                              Ontario                                   ON administrative_area_level_1, political
# 5                               Canada                                   CA                     country, political
# 6                                  L7S                                  L7S        postal_code, postal_code_prefix

或者类似地通过库(ggmap)，这也受到Google的2500个限制。

library(ggmap)

apply(Coords, 1, function(x){
    revgeocode(c(x["Longitude"], x["Latitude"]))
})

# 1 
# "Burlington Bay James N. Allan Skyway, Burlington, ON L7S, Canada" 
# 2 
# "308 Brennan Harbour Rd, Spanish, ON P0P 2A0, Canada" 
# 3 
# "724 Harris Ave, San Diego, CA 92154, USA" 
# 4 
# "30 Cherry St, Chinook, WA 98614, USA" 
# 5 
# "St James Township, MI, USA" 
# 6 
# "US-101, Chinook, WA 98614, USA" 
# 7 
# "2413 II Rd, Garden, MI 49835, USA" 
# 8 
# "2413 II Rd, Garden, MI 49835, USA" 
# 9 
# "8925 S Shore Rd, Stella, ON K0H 2S0, Canada" 
# 10 
# "Charlevoix County, MI, USA"