使用R将JSON文件转换为CSV文件
我有一个.txt文件中的JSON文件,我试图加载到R但我得到以下错误:
Error in feed_push_parser(readBin(con, raw(), n), reset = TRUE) :
parse error: trailing garbage
" : "SUCCESS" } /* 1 */ { "_id" : "b736c374-b8ae-4e9
(right here) ------^
我假设错误是因为/ *(数字)* /的多个实例,我无法手动将它们全部删除,因为我的文件具有这些10k实例。 在将数据加载到R之前是否有办法删除这些实例?
我的JSON文件如下所示:
/* 0 */
{
"_id" : "93ccbdb6-8947",
"uiSearchRequest" : {
"travelDate" : 20151206,
"travelDuration" : 7,
"shopperDuration" : 30,
"oneWay" : false,
"userId" : "ATP1KKP",
"queryId" : "93ccbdb6-8947",
"subRequests" : [{
"origin" : "WAS",
"destination" : "LON",
"carrier" : "AA",
"fareClasses" : "",
"owrt" : "1,2"
}]
},
"downloadCount" : 0,
"requestDate" : 20151205,
"totalRecords" : 0,
"status" : "SUCCESS"
}
/* 1 */
{
"_id" : "b736c374-b8ae",
"uiSearchRequest" : {
"travelDate" : 20151206,
"travelDuration" : 7,
"shopperDuration" : 30,
"oneWay" : false,
"userId" : "ATP1KKP",
"queryId" : "b736c374-b8ae",
"subRequests" : [{
"origin" : "WAS",
"destination" : "LON",
"carrier" : "AA",
"fareClasses" : "",
"owrt" : "1,2"
}]
},
"downloadCount" : 0,
"requestDate" : 20151205,
"totalRecords" : 0,
"status" : "SUCCESS"
}
/* 2 */
{
"_id" : "3312605f-8304",
"uiSearchRequest" : {
"travelDate" : 20151206,
"travelDuration" : 7,
"shopperDuration" : 30,
"oneWay" : false,
"userId" : "ATP1SXE",
"queryId" : "3312605f-8304",
"subRequests" : [{
"origin" : "LON",
"destination" : "IAD",
"carrier" : "AA",
"fareClasses" : "",
"owrt" : "1,2"
}]
},
"downloadCount" : 2,
"requestDate" : 20151205,
"totalRecords" : 0,
"status" : "SUCCESS"
}
/* 3 */
{
"_id" : "6b668cfa-9b79",
"uiSearchRequest" : {
"travelDate" : 20151206,
"travelDuration" : 7,
"shopperDuration" : 30,
"oneWay" : false,
"userId" : "ATP1NXA",
"queryId" : "6b668cfa-9b79",
"subRequests" : [{
"origin" : "WAS",
"destination" : "LON",
"carrier" : "AA",
"fareClasses" : "",
"owrt" : "1,2"
}]
},
"downloadCount" : 1,
"requestDate" : 20151205,
"totalRecords" : 1388,
"status" : "SUCCESS"
}
/* 4 */
{
"_id" : "41c373a1-e4cb",
"uiSearchRequest" : {
"travelDate" : 20151206,
"travelDuration" : 7,
"shopperDuration" : 30,
"oneWay" : false,
"userId" : "ATP6CXS",
"queryId" : "41c373a1-e4cb",
"subRequests" : [{
"origin" : "WAS",
"destination" : "LON",
"carrier" : "AA",
"fareClasses" : "",
"owrt" : "1,2"
}]
},
"downloadCount" : 0,
"requestDate" : 20151205,
"totalRecords" : 1388,
"status" : "SUCCESS"
}
/* 5 */
{
"_id" : "2c8331c4-21ca",
"uiSearchRequest" : {
"travelDate" : 20151206,
"travelDuration" : 7,
"shopperDuration" : 30,
"oneWay" : false,
"userId" : "ATP1KKP",
"queryId" : "2c8331c4-21ca",
"subRequests" : [{
"origin" : "WAS",
"destination" : "LON",
"carrier" : "AA",
"fareClasses" : "",
"owrt" : "1,2"
}]
},
"downloadCount" : 0,
"requestDate" : 20151205,
"totalRecords" : 1388,
"status" : "SUCCESS"
}
/* 6 */
{
"_id" : "71a09900-1c13",
"uiSearchRequest" : {
"travelDate" : 20151206,
"travelDuration" : 7,
"shopperDuration" : 30,
"oneWay" : false,
"userId" : "ATP6CXS",
"queryId" : "71a09900-1c13",
"subRequests" : [{
"origin" : "WAS",
"destination" : "LON",
"carrier" : "AF",
"fareClasses" : "",
"owrt" : "1,2"
}, {
"origin" : "WAS",
"destination" : "LON",
"carrier" : "AA",
"fareClasses" : "",
"owrt" : "1,2"
}, {
"origin" : "WAS",
"destination" : "LON",
"carrier" : "DL",
"fareClasses" : "",
"owrt" : "1,2"
}, {
"origin" : "WAS",
"destination" : "LON",
"carrier" : "LH",
"fareClasses" : "",
"owrt" : "1,2"
}, {
"origin" : "WAS",
"destination" : "LON",
"carrier" : "BA",
"fareClasses" : "",
"owrt" : "1,2"
}]
},
"downloadCount" : 0,
"requestDate" : 20151205,
"totalRecords" : 6941,
"status" : "SUCCESS"
}
/* 7 */
{
"_id" : "a036a42a-918b",
"uiSearchRequest" : {
"travelDate" : 20151206,
"travelDuration" : 7,
"shopperDuration" : 30,
"oneWay" : false,
"userId" : "ATP1MMM",
"queryId" : "a036a42a-918b",
"subRequests" : [{
"origin" : "WAS",
"destination" : "LON",
"carrier" : "AA",
"fareClasses" : "",
"owrt" : "1,2"
}]
},
"downloadCount" : 0,
"requestDate" : 20151205,
"totalRecords" : 1388,
"status" : "SUCCESS"
}
/* 8 */
{
"_id" : "c547be36-805c",
"uiSearchRequest" : {
"travelDate" : 20151206,
"travelDuration" : 7,
"shopperDuration" : 30,
"oneWay" : false,
"userId" : "ATP1SXB",
"queryId" : "c547be36-805c",
"subRequests" : [{
"origin" : "CHI",
"destination" : "LON",
"carrier" : "BA",
"fareClasses" : "",
"owrt" : "1,2"
}]
},
"downloadCount" : 2,
"requestDate" : 20151205,
"totalRecords" : 1072,
"status" : "SUCCESS"
}
我的代码在下面(虽然我没有得到太多):
library(jsonlite)
library(RJSONIO)
json_data_raw<-fromJSON("mydata.txt")
json_file <- lapply(json_data_raw, function(x) {
x[sapply(x, is.null)] <- NA
unlist(x)
})
output <-- do.call("rbind", json_file)
write.csv(a, file="json.csv",row.names = FALSE)
file.show("json.csv")
我正在尝试将我的输出转换为如下所示的CSV文件
你的文本文件有几个问题。 正如您已经注意到的那样,您需要删除表格/* 0 */
。 什么结果仍然是无效的json。 如果你想在一个文件中有多个json对象,你需要将它们存储在一个数组中。 json对象是在卷发器中关闭的部分,例如,
{
"_id" : "93ccbdb6-8947-4687-8e12-edf4e40d6650",
...
"totalRecords" : 0,
"status" : "SUCCESS"
}
对象数组的结构如下所示:
[
{
...
},
{
...
}
]
为了获得你的文件的形状,你需要在对象之间添加一个逗号并添加方括号。 你可以这样做,如下所示:
raw <- readLines("mydata.txt")
# get rid of the "/* 0 */" lines
json <- grep("^/* [0-9]* */", raw, value = TRUE, invert = TRUE)
# add missing comma after }
n <- length(json)
json[-n] <- gsub("^}$", "},", json[-n])
# add brakets at the beginning and end
json <- c("[", json, "]")
这可以通过fromJSON()
读取,所以我认为它是有效的json:
library(jsonlite)
table <- fromJSON(json)
该表是嵌套的,也就是说,某些表格单元格本身包含数据框或列表。 例如,
table[1,2]
## travelDate travelDuration shopperDuration oneWay userId queryId
## 1 20151206 7 30 FALSE ATP1KKP 93ccbdb6-8947-4687-8e12-edf4e40d6650
## subRequests
## 1 WAS, LON, AA, , 1,2
你可以使用jsonlite
包中的flatten()
来获得一个嵌套级别较少的表
flatten(table)[1:3, c(1, 6, 12)]
## _id uiSearchRequest.travelDate uiSearchRequest.subRequests
## 1 93ccbdb6-8947-4687-8e12-edf4e40d6650 20151206 WAS, LON, AA, , 1,2
## 2 b736c374-b8ae-4e99-8073-9c54517fecd5 20151206 WAS, LON, AA, , 1,2
## 3 3312605f-8304-4ab8-96d6-6e1a03cfbd9e 20151206 LON, IAD, AA, , 1,2
最后一列仍然是一个列表。 有很多方法可以处理这个问题。 一种可能性是为每个子请求创建一行,其中所有其他列的内容( X_id
, downloadCount
等)都会重复。 (这几乎是你在你的问题中给出的形式,唯一的区别是你在重复的列中留下了空单元格,而我重复了这些内容。)这是如何实现的:
table <- flatten(fromJSON(json))
tab_list <- lapply(1:nrow(table),
function(i) data.frame(table[i, -12], table[i, 12],
stringsAsFactors = FALSE))
library(dplyr)
flat_table <- bind_rows(tab_list)
第二行创建数据框的列表。 这些使用来自dpylr
bind_rows()
组合成一个数据框。 (更确切地说, flat_table
将是一个tbl_df
,但不同的data.frame
是小的。)这可以被写入以通常的方式csv文件:
write.csv(flat_table, file = "mydata.csv")
链接地址: http://www.djcxy.com/p/92339.html