r - Convert/export googleway output to data frame

Question

I'm trying to make sense and convert googleway.distance output into a data frame. I have a sample of 10 locations as below:

> origins
         V1        V2
1  38.82402 -78.28962
2  39.66405 -75.68834
3  38.68630 -77.30899
4  38.98991 -76.92997
5  39.26476 -77.88584
6  39.14255 -77.08824
7  38.95339 -77.16538
8  39.15798 -77.16514
9  39.03455 -77.47300
10 38.42632 -76.46342

> destinations
         V1        V2
1  38.90826 -78.20459
2  38.89980 -77.02137
3  38.87326 -77.05361
4  38.97834 -76.92821
5  39.25996 -77.88017
6  39.14281 -77.08835
7  38.84812 -77.07491
8  39.00266 -77.09257
9  38.84438 -77.11938
10 38.37362 -76.44139

My script and part of my output look like this:

res <- google_distance(origins, destinations, mode = c("driving", "walking",
                                            "bicycling", "transit"), departure_time = NULL, arrival_time = NULL,
            avoid = NULL, units = c("metric", "imperial"), traffic_model = NULL,
            transit_mode = NULL, transit_routing_preference = NULL, language = NULL,
            key = api_key, simplify = TRUE, curl_proxy = NULL)

> res$rows$elements
[[1]]
   distance.text distance.value   duration.text duration.value duration_in_traffic.text
1        17.6 km          17589         17 mins            993                  16 mins
2         131 km         130516  1 hour 37 mins           5802           1 hour 34 mins
3         129 km         128937  1 hour 30 mins           5405           1 hour 29 mins
4         152 km         152260  1 hour 50 mins           6596           1 hour 48 mins
5        72.0 km          71975   1 hour 7 mins           4000            1 hour 3 mins
6         157 km         156716  1 hour 45 mins           6305           1 hour 44 mins
7         133 km         132546  1 hour 33 mins           5577           1 hour 32 mins
8         133 km         132895  1 hour 32 mins           5496           1 hour 30 mins
9         132 km         131620  1 hour 31 mins           5467           1 hour 29 mins
10        226 km         226302 2 hours 33 mins           9166          2 hours 28 mins
   duration_in_traffic.value status
1                        973     OK
2                       5617     OK
3                       5315     OK
4                       6484     OK
5                       3789     OK
6                       6210     OK
7                       5493     OK
8                       5393     OK
9                       5343     OK
10                      8859     OK

[[2]]
   distance.text distance.value   duration.text duration.value duration_in_traffic.text
1         270 km         269899 2 hours 47 mins          10012          2 hours 45 mins
2         157 km         156825  1 hour 47 mins           6422           1 hour 44 mins
3         164 km         164106  1 hour 48 mins           6473           1 hour 44 mins
4         148 km         148312  1 hour 39 mins           5947           1 hour 37 mins
5         225 km         224905 2 hours 15 mins           8106          2 hours 14 mins
6         154 km         154192  1 hour 35 mins           5699           1 hour 35 mins
7         168 km         168099  1 hour 52 mins           6714           1 hour 48 mins
8         156 km         156140  1 hour 40 mins           5971           1 hour 38 mins
9         171 km         171489  1 hour 58 mins           7050           1 hour 52 mins
10        214 km         214136 2 hours 26 mins           8771          2 hours 20 mins
   duration_in_traffic.value status
1                       9895     OK
2                       6242     OK
3                       6253     OK
4                       5834     OK
5                       8053     OK
6                       5711     OK
7                       6462     OK
8                       5893     OK
9                       6749     OK
10                      8425     OK

> dput(res$rows$elements)
list(structure(list(distance = structure(list(text = c("17.6 km", 
"131 km", "129 km", "152 km", "72.0 km", "157 km", "133 km", 
"133 km", "132 km", "226 km"), value = c(17589L, 130516L, 128937L, 
152260L, 71975L, 156716L, 132546L, 132895L, 131620L, 226302L)), .Names = c("text", 
"value"), class = "data.frame", row.names = c(NA, 10L)), duration = structure(list(
    text = c("17 mins", "1 hour 37 mins", "1 hour 30 mins", "1 hour 50 mins", 
    "1 hour 7 mins", "1 hour 45 mins", "1 hour 33 mins", "1 hour 32 mins", 
    "1 hour 31 mins", "2 hours 33 mins"), value = c(993L, 5802L, 
    5405L, 6596L, 4000L, 6305L, 5577L, 5496L, 5467L, 9166L)), .Names = c("text", 
"value"), class = "data.frame", row.names = c(NA, 10L)), duration_in_traffic = structure(list(
    text = c("16 mins", "1 hour 34 mins", "1 hour 29 mins", "1 hour 48 mins", 
    "1 hour 3 mins", "1 hour 44 mins", "1 hour 32 mins", "1 hour 30 mins", 
    "1 hour 29 mins", "2 hours 28 mins"), value = c(973L, 5617L, 
    5315L, 6484L, 3789L, 6210L, 5493L, 5393L, 5343L, 8859L)), .Names = c("text", 
"value"), class = "data.frame", row.names = c(NA, 10L)), status = c("OK", 
"OK", "OK", "OK", "OK", "OK", "OK", "OK", "OK", "OK")), .Names = c("distance", 
"duration", "duration_in_traffic", "status"), class = "data.frame", row.names = c(NA, 
10L)),

This is just part of my output (it's too long so I cut it out); the whole result goes from [[1]] to [[10]]. Why 10 lists of 10 elements each? I chose 4 modes of transport (driving, walking, bicycling, transit), but the result seems to include only driving time and distance. Any way to include distance and time of all modes? How can I convert this list to a data frame?

These are methods that I tried:

newdf <- distance_elements(res) 
do.call(rbind.data.frame, newdf)

Error:

Error in `row.names<-.data.frame`(`*tmp*`, value = value) : 
  duplicate 'row.names' are not allowed
In addition: Warning message:
non-unique values when setting 'row.names': ‘1’, ‘10’, ‘2’, ‘3’, ‘4’, ‘5’, ‘6’, ‘7’, ‘8’, ‘9’ 

Then newdf1 <- ldply (newdf, data.frame):

Error in allocate_column(df[[var]], nrows, dfs, var) :
 Data frame column 'distance' not supported by rbind.fill

My desired output is distance and time of 1o pairs of lat/long (e.g., the 1st element of origins and 1st element of destinations, 2nd element of origins and 2nd element of destinations, etc.)

Answer 1

Google's Distance API

is a service that provides travel distance and time for a matrix of origins and destinations.

That is, you will get a distance for all the possible origin & destination combinations.

Given your description

My desired output is distance and time of 1o pairs of lat/long (e.g., the 1st element of origins and 1st element of destinations, 2nd element of origins and 2nd element of destinations, etc.)

You actually want just one value per origin/destination pair.

Also, the API can only accept one request at a time, so if you want to iterate over all O/D pairs, and all transport modes, you need to use a loop

Example

library(googleway)

set_key("your_api_key")

## iterate over each row of origins/destinaions
lst <- lapply(1:nrow(origins), function(x) {

    google_distance(origins = c(origins[x, "V1"], origins[x,"V2"]), 
                    destinations = c(destinations[x, "V1"], destinations[x, "V2"]),
                    mode = "driving",  ## you can only do one mode at a time
    )

})

## in the above iteration, we used 'lapply', so our results are stored in a list
## you have to access the specific elements/results from that list
lst_elements <- lapply(lst, function(x){
    stats::setNames(
        cbind(
          distance_elements(x)[[1]][['duration']],
          distance_elements(x)[[1]][['distance']]
        )
        , c("duration_text", "duration_value", "distance_text", "distance_value")
    )
})

## then you can start to create your data.frames (or data.table in this case)
dt_durations <- data.table::rbindlist(lst_elements)

#      duration_text duration_value distance_text distance_value
#  1:        17 mins            993       17.6 km          17589
#  2: 1 hour 47 mins           6429        158 km         158198
#  3:        33 mins           2009       38.6 km          38630
#  4:         8 mins            504        2.5 km           2466
#  5:         4 mins            225        1.5 km           1486
#  6:          1 min              1           2 m              2
#  7:        22 mins           1312       19.5 km          19495
#  8:        27 mins           1630       27.1 km          27094
#  9:        47 mins           2845       61.0 km          61024
# 10:         6 mins            364        7.0 km           7001

You'll have to do a similar 'loop' for iterating over the different modes

---

Going Further

If you want, you can also use the directions API to get driving routes between them

lst <- lapply(1:nrow(origins), function(x) {

    google_directions(origin = c(origins[x, "V1"], origins[x,"V2"]), 
                    destination = c(destinations[x, "V1"], destinations[x, "V2"]),
                    mode = "driving",  ## you can only do one mode at a time
    )

})

lst_elements <- lapply(lst, function(x){
    data.frame(
        polyline = direction_polyline(x)
    )
})

dt_routes <- data.table::rbindlist(lst_elements)


df_distances <- cbind(origins, destinations)
df_distances <- stats::setNames(df_distances, c("origin_lat", "origin_lon", "destination_lat", "destination_lon"))
df_distances <- cbind(df_distances, dt_routes, dt_durations)
df_distances$colour <- "blue" ## for colouring some markers
df_distances$info <- paste0("<b>Duration:</b>", df_distances$distance_value, 
                            "<br><b>Distance:</b>", df_distances$duration_value)

set_key("your_api_key", api = "map")

google_map(data = df_distances) %>%
    add_markers(lat = "origin_lat", lon = "origin_lon") %>%
    add_markers(lat = "destination_lat", lon = "destination_lon", colour = "colour") %>%
    add_polylines(polyline = "polyline", info_window = "info")