使用R将复杂的json解析为数据帧
问题描述：
我无法使用R解析JIRA中的以下复杂json输出到数据框。我能够使用PowerShell将数据部分解析为数据帧。但希望在R中完成这项工作。有人可以建议如何继续？此论坛中使用Rjson或jsonlite或RJSONIO列出的解决方案不适用于此示例。使用R将复杂的json解析为数据帧
[
{
"expand": [
"operations,versionedRepresentations,editmeta,changelog,renderedFields"
],
"id": [
"22513"
],
"self": [
"https://somecompany.atlassian.net/rest/api/latest/issue/22513"
],
"key": [
"DATA-1314"
],
"fields": {
"issuetype": {
"self": [
"https://somecompany.atlassian.net/rest/api/2/issuetype/10001"
],
"id": [
"10001"
],
"description": [
"A task that needs to be done."
],
"iconUrl": [
"https://somecompany.atlassian.net/secure/viewavatar?size=xsmall&avatarId=10318&avatarType=issuetype"
],
"name": [
"Task"
],
"subtask": [
false
],
"avatarId": [
10318
]
},
"timespent": {},
"project": {
"self": [
"https://somecompany.atlassian.net/rest/api/2/project/10300"
],
"id": [
"10300"
],
"key": [
"DATA"
],
"name": [
"Data"
],
"avatarUrls": {
"48x48": [
"https://somecompany.atlassian.net/secure/projectavatar?avatarId=10324"
],
"24x24": [
"https://somecompany.atlassian.net/secure/projectavatar?size=small&avatarId=10324"
],
"16x16": [
"https://somecompany.atlassian.net/secure/projectavatar?size=xsmall&avatarId=10324"
],
"32x32": [
"https://somecompany.atlassian.net/secure/projectavatar?size=medium&avatarId=10324"
]
}
},
"fixVersions": [],
"aggregatetimespent": {},
"resolution": {},
"customfield_10500": {},
"customfield_10700": {},
"resolutiondate": {},
"workratio": [
-1
],
"lastViewed": [
"2017-07-14T15:33:40.422-0700"
],
"watches": {
"self": [
"https://somecompany.atlassian.net/rest/api/2/issue/DATA-1314/watchers"
],
"watchCount": [
1
],
"isWatching": [
true
]
},
"created": [
"2017-07-12T11:56:12.000-0700"
],
"customfield_10022": {},
"customfield_10023": [
"1|i001zz:"
],
"priority": {
"self": [
"https://somecompany.atlassian.net/rest/api/2/priority/3"
],
"iconUrl": [
"https://somecompany.atlassian.net/images/icons/priorities/medium.svg"
],
"name": [
"Medium"
],
"id": [
"3"
]
},
"customfield_10300": {},
"labels": [
[
"data"
]
],
"customfield_10017": {},
"customfield_10018": {},
"timeestimate": {},
"aggregatetimeoriginalestimate": {},
"versions": [],
"issuelinks": [],
"assignee": {
"self": [
"https://somecompany.atlassian.net/rest/api/2/user?username=UserName"
],
"name": [
"UserName"
],
"key": [
"UserName"
],
"accountId": [
"1234567890"
],
"emailAddress": [
"[email protected]"
],
"avatarUrls": {
"48x48": [
"https://avatar-cdn.atlassian.com/12345678901206a6830d189deda8cc?s=48&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%12345678901215406a6830d189deda8cc%3Fd%3Dmm%26s%3D48%26noRedirect%3Dtrue"
],
"24x24": [
"https://avatar-cdn.atlassian.com/12345678901206a6830d189deda8cc?s=24&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%12345678901215406a6830d189deda8cc%3Fd%3Dmm%26s%3D24%26noRedirect%3Dtrue"
],
"16x16": [
"https://avatar-cdn.atlassian.com/12345678901206a6830d189deda8cc?s=16&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%12345678901215406a6830d189deda8cc%3Fd%3Dmm%26s%3D16%26noRedirect%3Dtrue"
],
"32x32": [
"https://avatar-cdn.atlassian.com/12345678901206a6830d189deda8cc?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%12345678901215406a6830d189deda8cc%3Fd%3Dmm%26s%3D32%26noRedirect%3Dtrue"
]
},
"displayName": [
"FName LName"
],
"active": [
true
],
"timeZone": [
"America/Los_Angeles"
]
},
"updated": [
"2017-07-12T13:07:51.000-0700"
],
"status": {
"self": [
"https://somecompany.atlassian.net/rest/api/2/status/1"
],
"description": [
"The issue is open and ready for the assignee to start work on it."
],
"iconUrl": [
"https://somecompany.atlassian.net/images/icons/statuses/open.png"
],
"name": [
"Open"
],
"id": [
"1"
],
"statusCategory": {
"self": [
"https://somecompany.atlassian.net/rest/api/2/statuscategory/2"
],
"id": [
2
],
"key": [
"new"
],
"colorName": [
"blue-gray"
],
"name": [
"To Do"
]
}
},
"components": [],
"timeoriginalestimate": {},
"description": {},
"customfield_10010": {},
"customfield_10011": {},
"customfield_10012": {},
"customfield_10013": {},
"customfield_10014": {},
"customfield_10015": {},
"customfield_10401": [
"FName"
],
"customfield_10600": {},
"customfield_10402": [
"TESTING DATA DOWNLOAD USING API"
],
"customfield_10006": {},
"customfield_10403": [
{
"self": [
"https://somecompany.atlassian.net/rest/api/2/customFieldOption/10100"
],
"value": [
"i_file"
],
"id": [
"10100"
]
}
],
"customfield_10007": {},
"customfield_10800": {},
"customfield_10008": {},
"aggregatetimeestimate": {},
"customfield_10009": {},
"summary": [
"testing"
],
"creator": {
"self": [
"https://somecompany.atlassian.net/rest/api/2/user?username=UserName"
],
"name": [
"UserName"
],
"key": [
"UserName"
],
"accountId": [
"1234567890"
],
"emailAddress": [
"[email protected]"
],
"avatarUrls": {
"48x48": [
"https://avatar-cdn.atlassian.com/12345678901206a6830d189deda8cc?s=48&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%12345678901215406a6830d189deda8cc%3Fd%3Dmm%26s%3D48%26noRedirect%3Dtrue"
],
"24x24": [
"https://avatar-cdn.atlassian.com/12345678901206a6830d189deda8cc?s=24&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%12345678901215406a6830d189deda8cc%3Fd%3Dmm%26s%3D24%26noRedirect%3Dtrue"
],
"16x16": [
"https://avatar-cdn.atlassian.com/12345678901206a6830d189deda8cc?s=16&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%12345678901215406a6830d189deda8cc%3Fd%3Dmm%26s%3D16%26noRedirect%3Dtrue"
],
"32x32": [
"https://avatar-cdn.atlassian.com/12345678901206a6830d189deda8cc?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%12345678901215406a6830d189deda8cc%3Fd%3Dmm%26s%3D32%26noRedirect%3Dtrue"
]
},
"displayName": [
"FName LName"
],
"active": [
true
],
"timeZone": [
"America/Los_Angeles"
]
},
"subtasks": [],
"reporter": {
"self": [
"https://somecompany.atlassian.net/rest/api/2/user?username=UserName"
],
"name": [
"UserName"
],
"key": [
"UserName"
],
"accountId": [
"1234567890"
],
"emailAddress": [
"[email protected]"
],
"avatarUrls": {
"48x48": [
"https://avatar-cdn.atlassian.com/12345678901206a6830d189deda8cc?s=48&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%12345678901215406a6830d189deda8cc%3Fd%3Dmm%26s%3D48%26noRedirect%3Dtrue"
],
"24x24": [
"https://avatar-cdn.atlassian.com/12345678901206a6830d189deda8cc?s=24&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%12345678901215406a6830d189deda8cc%3Fd%3Dmm%26s%3D24%26noRedirect%3Dtrue"
],
"16x16": [
"https://avatar-cdn.atlassian.com/12345678901206a6830d189deda8cc?s=16&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%12345678901215406a6830d189deda8cc%3Fd%3Dmm%26s%3D16%26noRedirect%3Dtrue"
],
"32x32": [
"https://avatar-cdn.atlassian.com/12345678901206a6830d189deda8cc?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%12345678901215406a6830d189deda8cc%3Fd%3Dmm%26s%3D32%26noRedirect%3Dtrue"
]
},
"displayName": [
"FName LName"
],
"active": [
true
],
"timeZone": [
"America/Los_Angeles"
]
},
"aggregateprogress": {
"progress": [
0
],
"total": [
0
]
},
"customfield_10000": {},
"customfield_10001": {},
"customfield_10002": {},
"customfield_10004": [
"{}"
],
"customfield_10400": [
"DA-000000"
],
"environment": {},
"duedate": [
"2017-07-13"
],
"progress": {
"progress": [
0
],
"total": [
0
]
},
"votes": {
"self": [
"https://somecompany.atlassian.net/rest/api/2/issue/DATA-1314/votes"
],
"votes": [
0
],
"hasVoted": [
false
]
}
}
}
]
答
这是我经常要做的事情。对我来说，我通常将json保存为R中的一个列表，然后根据我想要的数据框的外观创建一个数据框。这里是一些代码来说明：
library(tidyverse)
jira <- fromJSON(.....) #this is whatever code you used to get the data from JIRA in R
df <- data_frame(id = jira$id, fielddescription=jira$fields$description)
基本上你使用你创建构建自己的数据帧的jira对象中的元素。有时您需要使用多个$才能访问嵌套的项目，如上图所示jira$fields$description
您可能还想探索tidyjson包。它意味着这样的问题。我发现构建我自己的数据框比较简单，如图所示，但对于某些用例或编程风格，可能更适合您的口味和情况。
答
总结这一切在一组''，并将其分配给一个变量json。然后，
library(jsonlite)
l <- fromJSON(json, simplifyDataFrame = FALSE)
str(l)
正如其他人所说，这不会轻易地解析为一个data.frame，但在这里它是一个列表。
List of 1
$ :List of 5
..$ expand: chr "operations,versionedRepresentations,editmeta,changelog,renderedFields"
..$ id : chr "22513"
..$ self : chr "https://somecompany.atlassian.net/rest/api/latest/issue/22513"
..$ key : chr "DATA-1314"
..$ fields:List of 60
.. ..$ issuetype :List of 7
.. .. ..$ self : chr "https://somecompany.atlassian.net/rest/api/2/issuetype/10001"
.. .. ..$ id : chr "10001"
.. .. ..$ description: chr "A task that needs to be done."
.. .. ..$ iconUrl : chr "https://somecompany.atlassian.net/secure/viewavatar?size=xsmall&avatarId=10318&avatarType=issuetype"
.. .. ..$ name : chr "Task"
.. .. ..$ subtask : logi FALSE
.. .. ..$ avatarId : int 10318
.. ..$ timespent : Named list()
.. ..$ project :List of 5
.. .. ..$ self : chr "https://somecompany.atlassian.net/rest/api/2/project/10300"
.. .. ..$ id : chr "10300"
.. .. ..$ key : chr "DATA"
.. .. ..$ name : chr "Data"
.. .. ..$ avatarUrls:List of 4
.. .. .. ..$ 48x48: chr "https://somecompany.atlassian.net/secure/projectavatar?avatarId=10324"
.. .. .. ..$ 24x24: chr "https://somecompany.atlassian.net/secure/projectavatar?size=small&avatarId=10324"
.. .. .. ..$ 16x16: chr "https://somecompany.atlassian.net/secure/projectavatar?size=xsmall&avatarId=10324"
.. .. .. ..$ 32x32: chr "https://somecompany.atlassian.net/secure/projectavatar?size=medium&avatarId=10324"
.. ..$ fixVersions : list()
.. ..$ aggregatetimespent : Named list()
.. ..$ resolution : Named list()
.. ..$ customfield_10500 : Named list()
.. ..$ customfield_10700 : Named list()
.. ..$ resolutiondate : Named list()
.. ..$ workratio : int -1
.. ..$ lastViewed : chr "2017-07-14T15:33:40.422-0700"
.. ..$ watches :List of 3
.. .. ..$ self : chr "https://somecompany.atlassian.net/rest/api/2/issue/DATA-1314/watchers"
.. .. ..$ watchCount: int 1
.. .. ..$ isWatching: logi TRUE
.. ..$ created : chr "2017-07-12T11:56:12.000-0700"
.. ..$ customfield_10022 : Named list()
.. ..$ customfield_10023 : chr "1|i001zz:"
.. ..$ priority :List of 4
.. .. ..$ self : chr "https://somecompany.atlassian.net/rest/api/2/priority/3"
.. .. ..$ iconUrl: chr "https://somecompany.atlassian.net/images/icons/priorities/medium.svg"
.. .. ..$ name : chr "Medium"
.. .. ..$ id : chr "3"
.. ..$ customfield_10300 : Named list()
.. ..$ labels : chr [1, 1] "data"
.. ..$ customfield_10017 : Named list()
.. ..$ customfield_10018 : Named list()
.. ..$ timeestimate : Named list()
.. ..$ aggregatetimeoriginalestimate: Named list()
.. ..$ versions : list()
.. ..$ issuelinks : list()
.. ..$ assignee :List of 9
.. .. ..$ self : chr "https://somecompany.atlassian.net/rest/api/2/user?username=UserName"
.. .. ..$ name : chr "UserName"
.. .. ..$ key : chr "UserName"
.. .. ..$ accountId : chr "1234567890"
.. .. ..$ emailAddress: chr "[email protected]"
.. .. ..$ avatarUrls :List of 4
.. .. .. ..$ 48x48: chr "https://avatar-cdn.atlassian.com/12345678901206a6830d189deda8cc?s=48&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%1234567890121"| __truncated__
.. .. .. ..$ 24x24: chr "https://avatar-cdn.atlassian.com/12345678901206a6830d189deda8cc?s=24&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%1234567890121"| __truncated__
.. .. .. ..$ 16x16: chr "https://avatar-cdn.atlassian.com/12345678901206a6830d189deda8cc?s=16&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%1234567890121"| __truncated__
.. .. .. ..$ 32x32: chr "https://avatar-cdn.atlassian.com/12345678901206a6830d189deda8cc?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%1234567890121"| __truncated__
.. .. ..$ displayName : chr "FName LName"
.. .. ..$ active : logi TRUE
.. .. ..$ timeZone : chr "America/Los_Angeles"
.. ..$ updated : chr "2017-07-12T13:07:51.000-0700"
.. ..$ status :List of 6
.. .. ..$ self : chr "https://somecompany.atlassian.net/rest/api/2/status/1"
.. .. ..$ description : chr "The issue is open and ready for the assignee to start work on it."
.. .. ..$ iconUrl : chr "https://somecompany.atlassian.net/images/icons/statuses/open.png"
.. .. ..$ name : chr "Open"
.. .. ..$ id : chr "1"
.. .. ..$ statusCategory:List of 5
.. .. .. ..$ self : chr "https://somecompany.atlassian.net/rest/api/2/statuscategory/2"
.. .. .. ..$ id : int 2
.. .. .. ..$ key : chr "new"
.. .. .. ..$ colorName: chr "blue-gray"
.. .. .. ..$ name : chr "To Do"
.. ..$ components : list()
.. ..$ timeoriginalestimate : Named list()
.. ..$ description : Named list()
.. ..$ customfield_10010 : Named list()
.. ..$ customfield_10011 : Named list()
.. ..$ customfield_10012 : Named list()
.. ..$ customfield_10013 : Named list()
.. ..$ customfield_10014 : Named list()
.. ..$ customfield_10015 : Named list()
.. ..$ customfield_10401 : chr "FName"
.. ..$ customfield_10600 : Named list()
.. ..$ customfield_10402 : chr "TESTING DATA DOWNLOAD USING API"
.. ..$ customfield_10006 : Named list()
.. ..$ customfield_10403 :List of 1
.. .. ..$ :List of 3
.. .. .. ..$ self : chr "https://somecompany.atlassian.net/rest/api/2/customFieldOption/10100"
.. .. .. ..$ value: chr "i_file"
.. .. .. ..$ id : chr "10100"
.. ..$ customfield_10007 : Named list()
.. ..$ customfield_10800 : Named list()
.. ..$ customfield_10008 : Named list()
.. ..$ aggregatetimeestimate : Named list()
.. ..$ customfield_10009 : Named list()
.. ..$ summary : chr "testing"
.. ..$ creator :List of 9
.. .. ..$ self : chr "https://somecompany.atlassian.net/rest/api/2/user?username=UserName"
.. .. ..$ name : chr "UserName"
.. .. ..$ key : chr "UserName"
.. .. ..$ accountId : chr "1234567890"
.. .. ..$ emailAddress: chr "[email protected]"
.. .. ..$ avatarUrls :List of 4
.. .. .. ..$ 48x48: chr "https://avatar-cdn.atlassian.com/12345678901206a6830d189deda8cc?s=48&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%1234567890121"| __truncated__
.. .. .. ..$ 24x24: chr "https://avatar-cdn.atlassian.com/12345678901206a6830d189deda8cc?s=24&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%1234567890121"| __truncated__
.. .. .. ..$ 16x16: chr "https://avatar-cdn.atlassian.com/12345678901206a6830d189deda8cc?s=16&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%1234567890121"| __truncated__
.. .. .. ..$ 32x32: chr "https://avatar-cdn.atlassian.com/12345678901206a6830d189deda8cc?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%1234567890121"| __truncated__
.. .. ..$ displayName : chr "FName LName"
.. .. ..$ active : logi TRUE
.. .. ..$ timeZone : chr "America/Los_Angeles"
.. ..$ subtasks : list()
.. ..$ reporter :List of 9
.. .. ..$ self : chr "https://somecompany.atlassian.net/rest/api/2/user?username=UserName"
.. .. ..$ name : chr "UserName"
.. .. ..$ key : chr "UserName"
.. .. ..$ accountId : chr "1234567890"
.. .. ..$ emailAddress: chr "[email protected]"
.. .. ..$ avatarUrls :List of 4
.. .. .. ..$ 48x48: chr "https://avatar-cdn.atlassian.com/12345678901206a6830d189deda8cc?s=48&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%1234567890121"| __truncated__
.. .. .. ..$ 24x24: chr "https://avatar-cdn.atlassian.com/12345678901206a6830d189deda8cc?s=24&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%1234567890121"| __truncated__
.. .. .. ..$ 16x16: chr "https://avatar-cdn.atlassian.com/12345678901206a6830d189deda8cc?s=16&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%1234567890121"| __truncated__
.. .. .. ..$ 32x32: chr "https://avatar-cdn.atlassian.com/12345678901206a6830d189deda8cc?s=32&d=https%3A%2F%2Fsecure.gravatar.com%2Favatar%1234567890121"| __truncated__
.. .. ..$ displayName : chr "FName LName"
.. .. ..$ active : logi TRUE
.. .. ..$ timeZone : chr "America/Los_Angeles"
.. ..$ aggregateprogress :List of 2
.. .. ..$ progress: int 0
.. .. ..$ total : int 0
.. ..$ customfield_10000 : Named list()
.. ..$ customfield_10001 : Named list()
.. ..$ customfield_10002 : Named list()
.. ..$ customfield_10004 : chr "{}"
.. ..$ customfield_10400 : chr "DA-000000"
.. ..$ environment : Named list()
.. ..$ duedate : chr "2017-07-13"
.. ..$ progress :List of 2
.. .. ..$ progress: int 0
.. .. ..$ total : int 0
.. ..$ votes :List of 3
.. .. ..$ self : chr "https://somecompany.atlassian.net/rest/api/2/issue/DATA-1314/votes"
.. .. ..$ votes : int 0
.. .. ..$ hasVoted: logi FALSE
+0
该列表是我正在处理json输出。我无法超越此。 – RanonKahn
查看包'jsonlite'？ – CPak
这看起来不很矩形。你想要什么样的决赛桌？ – MrFlick