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, Jul 25 '19 at 14:26
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, Jul 25 '19 at 14:41
> sapply(res, class) [1] "WalkScore" "WalkScore" "WalkScore" "WalkScore" "WalkScore" "WalkScore" — , Jul 25 '19 at 14:44
Also, > as.data.frame(unclass(res)) Error in as.data.frame.default(x[[i]], optional = TRUE, stringsAsFactors = stringsAsFactors) : cannot coerce class ‘"WalkScore"’ to a data.frame — , Jul 25 '19 at 14:45
Yes the reprex I have put here is a representation of my dataset — , Jul 25 '19 at 14:46
No API access. Try this.`as.data.frame(sapply(res, unclass))` Hopefully someone with API access helps you solve it. — NelsonGon, Jul 25 '19 at 14:51
> as.data.frame(sapply(res, unclass)) V1 V2 status 1 1 walkscore 2 4 description Car-Dependent Car-Dependent updated 2019-03-28 21:43:37.670012 2019-04-11 11:23:51.651955 snappedLong -82.6365 -81.471 snappedLat 39.7545 40.851 — , Jul 25 '19 at 14:55
How can i get this in long format? like headers and values under them? — , Jul 25 '19 at 14:56
I cannot test, I have no data hence just giving directions. You can add the output of the above "solution" to the question so I(we) can have usable data. Also consider accepting answers to past questions if they solved your issue. Do so by clicking the tick below the answer that helped. — NelsonGon, Jul 25 '19 at 14:58
Can you provide the output `list` in reproducible form instead of the input dataset? How you collected the data is not really relevant to the question you're asking. — acylam, Jul 25 '19 at 15:31

the-mad-statter · Accepted Answer · 2019-07-25T17:35:52.730

1

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")

edited Jul 25 '19 at 17:35

answered Jul 25 '19 at 15:25

the-mad-statter

5,650
1
10
20

Hi Matthew, This worked. this may sound like a basic question but when i try to allocate this script to a vector and write to a csv i get an error > write.csv(df, file = "dfwalk.csv") Error in write.table(df, file = "dfwalk.csv", col.names = NA, sep = ",", : unimplemented type 'list' in 'EncodeElement'. How can I write this to a csv? – Jul 25 '19 at 16:59
See edits above. Apparently the old code did not produce a proper data.frame even though it looked like it when viewed with the print method. There is almost certainly a better way to do this, but the above works. – the-mad-statter Jul 25 '19 at 17:16
1

See re-edits that are more explicit about writing to csv. – the-mad-statter Jul 25 '19 at 17:36

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

show results

1 Answers1