Arihant Arihant - 4 months ago 89
JSON Question

JSON to dataframe in R

I have a text/html file with 158 row and 25 column data in JSON format and I have been trying to convert it into a dataframe so that I could write it in .csv. I have tried "rjson" and 'jsonlite' packages to read the data and then use convert it into datatable by two approach


  1. Use

    library(jsonlite)
    json_file = "projectslocations.html"
    json_datan <- fromJSON(json_file)



The data structure has only one row with 158 variables

2.using jsonlite and data.table

library(jsonlite)
library(data.table)
json_dat <- fromJSON(json_file)
class(json_dat)
lst= rbindlist(json_dat, fill=TRUE)


This shows data.frame with 158 rows and 25 variables. However I cant write this dataframe in csv or even view the dataframe.

error :

Error in FUN(X[[i]], ...) :
Invalid column: it has dimensions. Can't format it. If it's the result of data.table(table()), use as.data.table(table()) instead.


The original data is available here

Answer

Here's how I would munge your data using a bit of functional programming with the purrr package and the data-munging awesomeness of the dplyr package:

library(jsonlite) 
library(purrr)
library(dplyr)

# load JSON data and parse to list in R
json_file = file("projects.txt")
json_data <- fromJSON(json_file, simplifyDataFrame = FALSE)[[1]]

# extract location data seperately and create a data.frame with a project id column
locations <- 
  json_data %>% 
  at_depth(1, "locations") %>% 
  at_depth(2, ~data.frame(.x, stringsAsFactors = FALSE)) %>% 
  map(~bind_rows(.x)) %>% 
  bind_rows(.id = "id")

# prefix 'location_' to all location fields
colnames(locations) <- paste0("location_", colnames(locations))

# extract all project data excluding location data and create a data.frame
projects <- 
  json_data %>% 
  map(function(x) {x$locations <- NULL; x}) %>% 
  map(~data.frame(as.list(unlist(.x)), stringsAsFactors = FALSE)) %>% 
  bind_rows()

# join project and location data to yield a final denormalised data structure
projects_and_locations <- 
  projects %>% 
  inner_join(locations, by = c('id' = 'location_id'))

# details of single row of final denormalised data.frame
str(projects_and_locations[1,]) 

# 'data.frame': 1 obs. of  32 variables:
#   $ id                    : chr "P130343"
# $ project_name          : chr "MENA- Desert Ecosystems and Livelihoods Knowledge Sharing an"
# $ pl                    : chr "Global Environment Project"
# $ fy                    : chr "2013"
# $ ca                    : chr "$1.00M"
# $ gpname                : chr "Environment & Natural Resources"
# $ s                     : chr "Environment"
# $ ttl                   : chr "Taoufiq Bennouna"
# $ ttlupi                : chr "000314228"
# $ sbc                   : chr "ENV"
# $ sbn                   : chr "Environment"
# $ boardapprovaldate     : chr "23-May-2013"
# $ crd                   : chr "16-Feb-2012"
# $ dmd                   : chr ""
# $ ed                    : chr "10-Jun-2013"
# $ fdd                   : chr "04-Dec-2013"
# $ rcd                   : chr "31-Dec-2017"
# $ fc                    : chr "false"
# $ totalamt              : chr "$1.00M"
# $ url                   : chr "http://www.worldbank.org/projects/P130343?lang=en"
# $ project_abstract.cdata: chr ""
# $ sector.Name           : chr "Agriculture, fishing, and forestry"
# $ sector.code           : chr "AX"
# $ countrycode           : chr "5M"
# $ countryname           : chr "Middle East and North Africa"
# $ location_geoLocId     : chr "0002464470"
# $ location_url          : chr "javascript:projectPopupInfo('P130343', '0002464470')"
# $ location_geoLocName   : chr "Tunis"
# $ location_latitude     : chr "36.8190"
# $ location_longitude    : chr "10.1660"
# $ location_country      : chr "TN"
# $ location_countryName  : chr "Tunisia"