CODEWITHSUNDEEP
rjsontidyversetxt
strgrl
strgrl

Reputation: 25

Separating three JSON character and list data in R

I have a txt file of nested JSON data across three main tasks (entering ID information and two behavioural tasks) across several participants. I want to be able to fully flatten this file, and create an excel file at the end with the flattened information in the first string (rt 33588) and the second string (rt 33358 + "success....) and a second excel file with the first string (rt 3358 + "success...)

I have tried:

require(pacman)
p_load('tidyverse','jsonlite')

read_file('jsondata.txt') %>%
  str_split('\n') %>% first() %>%
  discard(function(x) x == '') %>%
  map_dfr(fromJSON, flatten=T) -> mydata

However this returns the error code:

Error: Can't combine `..1$responses` <character> and `..2$responses` <list>.

This seems to be a tidyverse error that I can't figure out how to resolve. If I split up the txt file manually I am able to run the above code on each of the three strings and create two excel files, but there must be a way to read a single txt file and split it?

My JSON data is below:

[{"rt":33588,"responses":"{\"School_ID\":\"12345\",\"Class_ID\":\"12345\"}","trial_type":"survey-text-image","trial_index":0,"time_elapsed":33606,"internal_node_id":"0.0-0.0"},{"rt":4424,"responses":"{\"Participant_ID\":\"1833\"}","trial_type":"survey-text-image","trial_index":1,"time_elapsed":38064,"internal_node_id":"0.0-1.0-0.0-0.0"}]
[{"success":true,"timeout":false,"failed_images":[],"failed_audio":[],"failed_video":[],"trial_type":"preload","trial_index":0,"time_elapsed":152,"internal_node_id":"0.0-0.0"},{"rt":6004,"stimulus":"<div><h1> Task 1 </h1></div></div>","response":0,"trial_type":"html-button-response-modified","trial_index":1,"time_elapsed":6172,"internal_node_id":"0.0-1.0"},{"responses":[{"image_id":"p_ts1","image":"img/target.png","rt":1062,"correct":true,"coord_x":"501","coord_y":"205","target":"true","side":"C","height":"34.5","width":"33.7"},{"image_id":"p_ts2","image":"img/target.png","rt":1444,"correct":true,"coord_x":"501","coord_y":"258","target":"true","side":"C","height":"34.5","width":"33.7"},{"image_id":"p_ts3","image":"img/target.png","rt":2177,"correct":true,"coord_x":"501","coord_y":"507","target":"true","side":"C","height":"34.5","width":"33.7"}],"trial_type":"target-cancellation","trial_index":2,"time_elapsed":10108,"internal_node_id":"0.0-2.0-0.0"},{"rt":1003,"stimulus":"<div><h1> Main Task</h1></div>","response":0,"trial_type":"html-button-response-modified","trial_index":3,"time_elapsed":11123,"internal_node_id":"0.0-3.0"},{"responses":[{"image_id":"ts1","image":"img/target.png","rt":1264,"correct":true,"coord_x":"932","coord_y":"39","target":"true","side":"R","height":"34.5","width":"33.7"},{"image_id":"ts2","image":"img/target.png","rt":1484,"correct":true,"coord_x":"880","coord_y":"77","target":"true","side":"R","height":"34.5","width":"33.7"},{"image_id":"ts11","image":"img/target.png","rt":2039,"correct":true,"coord_x":"744","coord_y":"53","target":"true","side":"R","height":"34.5","width":"33.7"}],"trial_type":"target-cancellation","trial_index":4,"time_elapsed":27258,"internal_node_id":"0.0-4.0"},{"rt":1713.0000000000036,"stimulus":"<div><h1> Finish Task</h1><img id='stars' src='img/target.png'></img></div>","response":0,"trial_type":"html-button-response-modified","trial_index":5,"time_elapsed":28984,"internal_node_id":"0.0-5.0"}]
[{"success":true,"timeout":false,"failed_images":[],"failed_audio":[],"failed_video":[],"trial_type":"preload","trial_index":0,"time_elapsed":156,"internal_node_id":"0.0-0.0"},{"rt":1794,"stimulus":"<div><h1> Task 2</h1></div>","response":0,"trial_type":"html-button-response-modified","trial_index":1,"time_elapsed":1959,"internal_node_id":"0.0-1.0"},{"rt":null,"stimulus":"img/FixationCross.png","response":null,"test_part":"practice_fixation","trial_type":"image-button-response","trial_index":2,"time_elapsed":5311,"internal_node_id":"0.0-2.0-0.0"},{"rt":1082.999999999999,"stimulus":"img/target.png","response":1,"test_part":"Practice Test","condition":"Medium","type":"Large","target":"two","correct_response":1,"trial_type":"image-button-response","trial_index":3,"time_elapsed":6404,"internal_node_id":"0.0-2.0-1.0","correct":true},{"rt":null,"stimulus":"img/FeedbackTick.png","response":null,"trial_type":"image-button-response","trial_index":4,"time_elapsed":7411,"internal_node_id":"0.0-2.0-2.0"},{"rt":null,"stimulus":"img/FixationCross.png","response":null,"test_part":"practice_fixation","trial_type":"image-button-response","trial_index":5,"time_elapsed":13421,"internal_node_id":"0.0-2.0-0.1"},{"rt":971,"stimulus":"img/SmallSun.png","response":0,"test_part":"Practice Test","condition":"Extra Long","type":"Small","target":"left","correct_response":0,"trial_type":"image-button-response","trial_index":6,"time_elapsed":14421,"internal_node_id":"0.0-2.0-1.1","correct":true},{"rt":null,"stimulus":"img/FeedbackTick.png","response":null,"trial_type":"image-button-response","trial_index":7,"time_elapsed":15429,"internal_node_id":"0.0-2.0-2.1"},{"rt":null,"stimulus":"img/FixationCross.png","response":null,"test_part":"practice_fixation","trial_type":"image-button-response","trial_index":8,"time_elapsed":16577,"internal_node_id":"0.0-2.0-0.2"},{"rt":1000,"stimulus":"img/SmallStar.png","response":1,"test_part":"Practice Test","condition":"Short","type":"Small","target":"right","correct_response":1,"trial_type":"image-button-response","trial_index":9,"time_elapsed":17587,"internal_node_id":"0.0-2.0-1.2","correct":true},{"rt":null,"stimulus":"img/FeedbackTick.png","response":null,"trial_type":"image-button-response","trial_index":10,"time_elapsed":18594,"internal_node_id":"0.0-2.0-2.2"},{"rt":null,"stimulus":"img/FixationCross.png","response":null,"test_part":"practice_fixation","trial_type":"image-button-response","trial_index":11,"time_elapsed":21369,"internal_node_id":"0.0-2.0-0.3"},{"rt":1037,"stimulus":"img/MediumSun.png","response":0,"test_part":"Practise Test","condition":"Medium","type":"Medium","target":"right","correct_response":0,"trial_type":"image-button-response","trial_index":12,"time_elapsed":22415,"internal_node_id":"0.0-2.0-1.3","correct":true},{"rt":null,"stimulus":"img/FeedbackTick.png","response":null,"trial_type":"image-button-response","trial_index":13,"time_elapsed":23428,"internal_node_id":"0.0-2.0-2.3"},{"rt":1621,"stimulus":"<div><h1> Main Task </h1></div>","response":0,"trial_type":"html-button-response-modified","trial_index":20,"time_elapsed":33659,"internal_node_id":"0.0-3.0"},{"rt":null,"stimulus":"img/FixationCross.png","response":null,"test_part":"fixation","trial_type":"image-button-response","trial_index":21,"time_elapsed":37990,"internal_node_id":"0.0-4.0-0.0"},{"rt":998,"stimulus":"img/target.png","response":0,"test_part":"test","condition":"Medium","type":"Medium","target":"left","correct_response":0,"trial_type":"image-button-response","trial_index":22,"time_elapsed":39004,"internal_node_id":"0.0-4.0-1.0","correct":true},{"rt":886,"stimulus":"img/target.png","response":0,"test_part":"test","condition":"Long","type":"Medium","target":"left","correct_response":0,"trial_type":"image-button-response","trial_index":80,"time_elapsed":147593,"internal_node_id":"0.0-4.0-1.29","correct":true},{"rt":1369,"stimulus":"<div><h1> End Task Race </h1></div>","response":0,"trial_type":"html-button-response-modified","trial_index":99,"time_elapsed":180056,"internal_node_id":"0.0-5.0"}]

Any help is greatly appreciated - I do not normally work with JSON data so any guidance is extremely helpful!

Upvotes: 2

Views: 126

Answers (1)

Sinh Nguyen
Sinh Nguyen

Reputation: 4497

The error is cause by your text data contain 3 json with different structure so when mapped with map_dfr they resulted in 3 df with slightly different structure that map_dfr couldn't combine them. In this case the responses column in the 1st json string is character while 2nd json responses is a list

library('tidyverse')
library('jsonlite')

text_data <- read_lines('jsondata.txt')

df_1 <- fromJSON(text_data[1], flatten = TRUE)
class(df_1$responses)
#> [1] "character"

df_2 <- fromJSON(text_data[2], flatten = TRUE)
class(df_2$responses)
#> [1] "list"

if you only need to read the data together but not put them into one data.frame then you can do this

library('tidyverse')
library('jsonlite')

text_data <- read_lines('jsondata.txt')

json_data <- map(text_data, fromJSON, flatten = TRUE)
summary(json_data)
#>      Length Class      Mode
#> [1,]  6     data.frame list
#> [2,] 13     data.frame list
#> [3,] 18     data.frame list

Created on 2021-05-20 by the reprex package (v2.0.0) Created on 2021-05-20 by the reprex package (v2.0.0)

Upvotes: 1

Related Questions