JSON到R中的数据帧 [英] JSON to dataframe in R

问题描述

我有一个文本/ html文件与158行和25列数据JSON格式，我一直在尝试将其转换为一个数据帧，所以我可以写在.CSV。我尝试了rjson和jsonlite包来读取数据，然后通过两种方法将其转换为datatable：

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. 使用

1. Use

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

数据结构只有一行有158个变量

The data structure has only one row with 158 variables

2.使用jsonlite和data.table

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)

这显示data.frame包含158行和25个变量。但是我不能在csv中写这个数据框，甚至查看数据框。

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

错误：

 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.

原始数据可用此处

推荐答案

将使用一些功能编程，使用purrr软件包和dplyr软件包中的数据处理功能来创建数据：

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" 

这篇关于JSON到R中的数据帧的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！