How do I convert my walkscoreAPI output list to a dataframe in R?

Question

I have used the walkscore API to generate output for a list of Lat and Longs

Reprex of dataset:

tibble::tribble(
         ~Lat,        ~Long,
  39.75454546, -82.63637088,
  40.85117794, -81.47034464,
  40.53956136, -74.33630685,
  42.16066679, -71.21368025,
  39.27048579, -119.5770782,
  64.82534285, -147.6738774
  )

My code:

library(walkscoreAPI)
library(rjson)
data = read.csv(file="geocode_finalcompiled.csv", header=TRUE, sep=",")
attach(data)
#create empty list
res = list()
# for loop through a file

for(i in 1:500){
  res[i] = list(getWS(data$Long[i],data$Lat[i],"mykey"))

}

show results

res

> res
[[1]]
$status
[1] 1

$walkscore
[1] 2

$description
[1] "Car-Dependent"

$updated
[1] "2019-03-28 21:43:37.670012"

$snappedLong
[1] -82.6365

$snappedLat
[1] 39.7545

As you can see the output is in json format. My objective is to make this into a dataframe where each value is displayed under each header and can be put into a csv.

I tried:

resformatted <- as.data.frame(res)

But got the below error:

Error in as.data.frame.default(x[[i]], optional = TRUE, stringsAsFactors = stringsAsFactors) : cannot coerce class ‘"WalkScore"’ to a data.frame

What can be done to fix this?

The output is a list not json or am I wrong? Unclass it and make it a df as required. — NelsonGon
Cannot test since I don't have this package installed. Try as.data.frame(unclass(res)). Alternatively, what is the output of sapply(res, class)? — NelsonGon
> sapply(res, class) [1] "WalkScore" "WalkScore" "WalkScore" "WalkScore" "WalkScore" "WalkScore" — user11777262
Yes the reprex I have put here is a representation of my dataset — user11777262

the-mad-statter the-mad-statter · Accepted Answer · 2019-07-25T15:25:47

Going off the above approach:

library(dplyr)
library(tibble)

res %>% 
  sapply(unclass) %>% 
  as.data.frame() %>% 
  t() %>% 
  as.data.frame() %>% 
  lapply(unlist) %>% 
  as.data.frame(stringsAsFactors = FALSE) %>% 
  remove_rownames() -> df

Produces:

#   status walkscore       description                    updated snappedLong snappedLat
# 1      1         2     Car-Dependent 2019-03-28 21:43:37.670012    -82.6365    39.7545
# 2      1         4     Car-Dependent 2019-04-11 11:23:51.651955     -81.471     40.851
# 3      1        60 Somewhat Walkable 2019-02-25 01:05:08.918498     -74.337     40.539
# 4      1        44     Car-Dependent 2019-04-17 16:26:58.848496     -71.214    42.1605
# 5      1        16     Car-Dependent 2019-05-09 01:34:59.741290    -119.577      39.27
# 6      1         0     Car-Dependent 2019-07-22 19:27:50.170107   -147.6735    64.8255

And write to csv with:

write.csv(df, file = "dfwalk.csv")

How do I convert my walkscoreAPI output list to a dataframe in R?

show results

1 Answers