如何在R中选择JSON数据的特定部分?

本文介绍了如何在R中选择JSON数据的特定部分?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在尝试将url中的数据以JSON形式导入R，然后将其导出到Excel文件中.

I am trying to import data from url into R which is in the form of JSON, then export it to an Excel file.

URL : api. typeform/v1/form/JlCM2J?key=ecab3f590a2af4ca55468adc95686a043bbf6c9a

这是我的R代码

library(data.table) library(httr) library(rjson) set_config(config(ssl_verifypeer = 0L)) var1=fread('api.typeform/v1/form/JlCM2J?key=ecab3f590a2af4ca55468adc95686a043bbf6c9a') head(var1)

输出:

空数据表(0行)，共161个列:{"http_status":200，stats:{" responses:{" showing:2，" total:2，" completed:1}} ，"questions":[{"id":"textfield_38991412，question":您的名字是什么?，field_id":38991412}，{"id":"statement_38991416，question":{{answer_38991412}}，感谢您接受这份问卷调查.您的回答将帮助我们为您打造一个伟大的品牌.在您的客户心目中，这是一个强大而令人难忘的品牌.一个清楚地定义您的身份，代表什么以及使您与众不同的东西.让我们开始吧！field_id:38991416}，{" id:" group_38991407 ...

Empty data.table (0 rows) of 161 cols: {"http_status":200,stats":{"responses":{"showing":2,"total":2,"completed":1}},"questions":[{"id":"textfield_38991412,question":"What is your first name?,field_id":38991412},{"id":"statement_38991416,question":"Hi {{answer_38991412}},Thank you for taking this questionnaire.Your answers will help us build a great brand for you. One that is strong and memorable in your customers' minds. One that defines clearly what you are, what you stand for, and what makes you different.Let's get started!,field_id":38991416},{"id":"group_38991407...

需要: 我只需要将此数据的responses部分导出为Excel文件即可. 可以通过将上面的URL粘贴到jsonviewer.stack.hu网站

Need: I only need the responses section of this data to be exported as an Excel file. Entire data can be viewed by pasting the above url in the jsonviewer.stack.hu site

推荐答案

以下是与现代R中的REST API交互的(IMO)惯用方式:

The following is the more (IMO) idiomatic way to interface with REST APIs in modern R:

library(httr) library(jsonlite) library(dplyr) res <- GET("api.typeform/v1/form/JlCM2J", query=list(key="ecab3f590a2af4ca55468adc95686a043bbf6c9a")) content(res, as="text") %>% fromJSON(flatten=FALSE) -> out glimpse(out$responses$answers) ## Observations: 2 ## Variables: 26 ## $ textfield_38991412 <chr> NA, "A" ## $ dropdown_38991418 <chr> NA, "Accounting" ## $ textarea_38991420 <chr> NA, "A" ## $ textfield_38991413 <chr> NA, "A" ## $ textarea_38991421 <chr> NA, "A" ## $ listimage_38991426_choice <chr> NA, "Company" ## $ textfield_38991414 <chr> NA, "A" ## $ website_38991435 <chr> NA, "A" ## $ textarea_38991422 <chr> NA, "A" ## $ listimage_38991427_choice <chr> NA, "Sincere" ## $ listimage_38991428_choice <chr> NA, "Male" ## $ list_38991436_choice <chr> NA, "17 or younger" ## $ list_38991437_choice <chr> NA, "Upper class" ## $ listimage_38991429_choice_49501105 <chr> NA, "Store" ## $ listimage_38991430_choice <chr> NA, "Product" ## $ textarea_38991423 <chr> NA, "A" ## $ listimage_38991431_choice <chr> NA, "Techy" ## $ listimage_38991432_choice_49501124 <chr> NA, "Fuchsia Rose" ## $ listimage_38991433_choice <chr> NA, "Classic" ## $ list_38991438_choice <chr> NA, "$3,000 or less" ## $ listimage_38991434_choice_49501140 <chr> NA, "Brand Design" ## $ textarea_38991424 <chr> NA, "A" ## $ textfield_38991415 <chr> NA, "A" ## $ dropdown_38991419 <chr> NA, "Afghanistan" ## $ email_38991439 <chr> NA, "A@a" ## $ textarea_38991425 <chr> NA, "A"

• 直接使用httr::GET()可以更轻松地管理其他参数.
• 使用httr::content()获取响应并检索原始文本可以进行更细粒度的处理(如果需要)
• 直接使用jsonlite::fromJSON()可以对单个JSON处理选项进行更精细的控制.
• Using httr::GET() directly will enable easier management of extra parameters.
• Using httr::content() to take the response and retrieve the raw text enables finer-grained processing (if needed)
• Using jsonlite::fromJSON() directly provides far more granular control over individual JSON processing options.

但是，有一个R包rtypeform确实简化了所有事情(有趣的事实:它遵循上面的成语，位于幕后):

However, there's an R package rtypeform which really simplifies everything (fun fact: it follows the idiom above under the covers):

library(rtypeform) library(dplyr) res <- get_results("JlCM2J") glimpse(res$responses$answers) ## Observations: 2 ## Variables: 26 ## $ textfield_38991412 <chr> NA, "A" ## $ dropdown_38991418 <chr> NA, "Accounting" ## $ textarea_38991420 <chr> NA, "A" ## $ textfield_38991413 <chr> NA, "A" ## $ textarea_38991421 <chr> NA, "A" ## $ listimage_38991426_choice <chr> NA, "Company" ## $ textfield_38991414 <chr> NA, "A" ## $ website_38991435 <chr> NA, "A" ## $ textarea_38991422 <chr> NA, "A" ## $ listimage_38991427_choice <chr> NA, "Sincere" ## $ listimage_38991428_choice <chr> NA, "Male" ## $ list_38991436_choice <chr> NA, "17 or younger" ## $ list_38991437_choice <chr> NA, "Upper class" ## $ listimage_38991429_choice_49501105 <chr> NA, "Store" ## $ listimage_38991430_choice <chr> NA, "Product" ## $ textarea_38991423 <chr> NA, "A" ## $ listimage_38991431_choice <chr> NA, "Techy" ## $ listimage_38991432_choice_49501124 <chr> NA, "Fuchsia Rose" ## $ listimage_38991433_choice <chr> NA, "Classic" ## $ list_38991438_choice <chr> NA, "$3,000 or less" ## $ listimage_38991434_choice_49501140 <chr> NA, "Brand Design" ## $ textarea_38991424 <chr> NA, "A" ## $ textfield_38991415 <chr> NA, "A" ## $ dropdown_38991419 <chr> NA, "Afghanistan" ## $ email_38991439 <chr> NA, "A@a" ## $ textarea_38991425 <chr> NA, "A"

无论哪种方式，如果您不习惯使用$访问列表中的字段，这都必须是您第一次使用R(或者几乎是第一次).在尝试使用API​​数据之前，您应该花一些时间学习R.错误的结果和自我挫败感是您通过编码粘贴并祈祷的唯一回报.您仍然需要将此文件保存为CSV文件(为此write.csv()).

Either way, this must be your first time using R (or almost your first time) if you aren't used to using $ to access fields in lists. You should really spend some time learning R before trying to work with API data. Incorrect results and self-frustration are the only things you're going to get in return for cut-paste-and-praying your way through coding. You still need to get this into a CSV file (write.csv() for that).

最后，如果您最终要使用Excel，为什么要以编程方式检索表单响应?如果您不打算在其余工作中使用R，那么这似乎是一个不必要的步骤，除非您尝试编写数据下载脚本或让某人登录该站点以获取数据.

Penultimately, if you're just going to end up using Excel, why are you programmatically retrieving the form responses? If you're not going to use R for the rest of the work, this seems like a needless step unless you're trying to script the download of the data vs make someone login to the site to fetch it.

最后，由于您将其发布在开放论坛中，因此立即使您的API密钥无效并重新生成.我现在知道您有2种表单:品牌问卷"和测试表单"，并且将能够监视您在Typeform中所做的工作并随意检索任何表单数据. rtypeform软件包将使您可以将API密钥存储在typeform_api环境变量中(可以使用~/.Renviron来保存此数据)，因此您无需在脚本中再次将其公开.

Finally, invalidate and re-generate your API key immediately since you posted it in an open forum. I now know you have 2 forms: "Branding Questionnaire" and "Test Form" and will be able to monitor what you do in Typeform and retrieve any of your form data at-will. The rtypeform package will let you store your API key in the typeform_api environment variable (you can use the ~/.Renviron to hold this data) so you never have to expose it to the world again in your scripts.