How to get fromJSON list to data frame?

Question

I am new to json data and need help in disentangling the data in to a data frame..

The str(url) shows that this is a list containing lists and data frames. I searched the interweb, but found no simple solution to the problem..

library(jsonlite)

url <- fromJSON("http://data.ssb.no/api/v0/no/table/03886")

str(url)

I would like to have this as a data frame witch I am familiar with.

 df<-as.data.frame(url)

Use the output of "str" to understand the data structure and build the data.frames you want. From what I see there are not an obvious single data.frames to build, rather 4 simple ones. You can access elements of a list with the double bracket `[[` operator. — asachet, Jun 22 '16 at 13:15
For example something hacky like this would create a list (called df_list) of 4 data.frames. You can access the first data.frame with `dfs_list[[1]]`. `df_list = list(); for(i in 1:4) {df_list[[tmp$variables$code[i]]] <- data.frame(val=tmp$variables$values[[i]], description=tmp$variables$valueTexts[[i]])}`. — asachet, Jun 22 '16 at 13:17

score 1 · Accepted Answer · answered Jun 29 '16 at 05:42

Given the JSON structure contains nested key:value and key:arrays, you can't get a simple data.frame directly with your JSON data. You need to access the specific components and convert those to a data.frame

For example, in the data you've provided we now that url is a list

str(url)
# List of 2
# ...
# the two elements being
names(url)
# [1] "title"     "variables"

So we can access these elements

str(url$title)
# chr "Felte småvilt, etter region, småvilt og intervall (år)"
str(url$variables)
# 'data.frame': 4 obs. of  6 variables:
#   $ code       : chr  "Region" "Smaviltjakt" "ContentsCode" "Tid"
# $ text       : chr  "region" "småvilt" "statistikkvariabel" "intervall (år)"
# $ values     :List of 4
# ..$ : chr  "0" "01" "02" "03" ...
# ..$ : chr  "00" "01" "02" "03" ...
# ..$ : chr "Smaavilt"
# ..$ : chr  "1991-1992" "1992-1993" "1993-1994" "1994-1995" ...

You now have to work out what specific data components you want.

Borrowing from @antoine-sac 's comment, we can create a list of four data.frames:

df_list = list(); 
for(i in 1:4) {
    df_list[[url$variables$code[i]]] <- data.frame(val=url$variables$values[[i]], 
    description=url$variables$valueTexts[[i]])
}

Finally; you should get familar working with lists, not just data.frames. They are important in R.

How to get fromJSON list to data frame?

1 Answers1