I have a problem when trying to expand a dataframe that contain two rows with a JSON string in a column. My intention is to expand the different layers into different new columns and rows on the dataframe.
#Reproducible csv:
json.csv <- r"("","object_name","response"
"1","B3 reflection","[{""question"":{""id"":46445,""text"":""What did you see in this module that you already do or have seen in other classrooms?"",""question_id"":1,""requirements"":{""answer_type"":""text"",""text_required"":true}},""response"":{""free_text"":{""id"":""46445_ft"",""text"":1}}},{""question"":{""id"":46446,""text"":""What do you feel is the gap between your current practice and what you have seen in this module?"",""question_id"":2,""requirements"":{""answer_type"":""text"",""text_required"":true}},""response"":{""free_text"":{""id"":""46446_ft"",""text"":1}}}]"
"2","B1 Reflection","[{""question"":{""id"":22061,""text"":""What did you see in this module that you already do or have seen in other classrooms?"",""question_id"":135,""requirements"":{""answer_type"":""text"",""text_required"":true}},""response"":{""free_text"":{""id"":""22061_ft"",""text"":""Strong classroom routines and high expectations""}}},{""question"":{""id"":22062,""text"":""What do you feel is the gap between your current practice and what you have seen in this module?"",""question_id"":136,""requirements"":{""answer_type"":""text"",""text_required"":true}},""response"":{""free_text"":{""id"":""22062_ft"",""text"":""Making sure I am consistent in classroom routines""}}},{""question"":{""id"":22063,""text"":""Which of the \u2018key takeaways\u2019 do you need to focus on? Where and when might you try to apply them to your teaching?"",""question_id"":137,""requirements"":{""answer_type"":""text"",""text_required"":true}},""response"":{""free_text"":{""id"":""22063_ft"",""text"":""Holding high ...""}}}]")"
Let's load the data using a text connection:
json_map = read.csv(text = json.csv)
I am using this code in dplyr to expand row wise the previous two rows from the dataframe and it works perfectly well when I expand every row individually and when I do that individually it works perfectly well for everyone of the two rows
#Already tried with these two and work perfectly fine
json_map = json_map[1,]
json_map = json_map[2,]
json_map = json_map |>
rowwise() |>
mutate(response = map(response, jsonlite::fromJSON)) |>
unnest_longer(response) |>
unnest_wider(c(response), names_sep = "_") |>
unnest_wider(c(response_response, response_question), names_sep = "_") |>
unnest_longer(c(response_response_free_text, response_question_requirements))|>
unnest_wider(response_response_free_text, names_sep = "_") |>
unnest_longer(response_question_requirements)|>
unnest_wider(response_question_requirements, names_sep = "_")
Now, when I try to expand with all the dataframe json_map at the same time, the operation returns this error:
Error in `col_to_long()`:
! Can't combine `..1$response$response$free_text$text` <integer> and `..3$response$response$free_text$text` <character>.
Run `rlang::last_trace()` to see where the error occurred.
From my understanding the only difference in both is that the $response$response$free_text$text layer contains an integer instead a character, so I tried to add mutate_all(as.character) at the end expecting it would convert everything into strings but it returns the same exact error.
Any insights are appreciated.
library(tidyverse)
library(jsonlite)
json.csv <- r"("","object_name","response"
"1","B3 reflection","[{""question"":{""id"":46445,""text"":""What did you see in this module that you already do or have seen in other classrooms?"",""question_id"":1,""requirements"":{""answer_type"":""text"",""text_required"":true}},""response"":{""free_text"":{""id"":""46445_ft"",""text"":1}}},{""question"":{""id"":46446,""text"":""What do you feel is the gap between your current practice and what you have seen in this module?"",""question_id"":2,""requirements"":{""answer_type"":""text"",""text_required"":true}},""response"":{""free_text"":{""id"":""46446_ft"",""text"":1}}}]"
"2","B1 Reflection","[{""question"":{""id"":22061,""text"":""What did you see in this module that you already do or have seen in other classrooms?"",""question_id"":135,""requirements"":{""answer_type"":""text"",""text_required"":true}},""response"":{""free_text"":{""id"":""22061_ft"",""text"":""Strong classroom routines and high expectations""}}},{""question"":{""id"":22062,""text"":""What do you feel is the gap between your current practice and what you have seen in this module?"",""question_id"":136,""requirements"":{""answer_type"":""text"",""text_required"":true}},""response"":{""free_text"":{""id"":""22062_ft"",""text"":""Making sure I am consistent in classroom routines""}}},{""question"":{""id"":22063,""text"":""Which of the \u2018key takeaways\u2019 do you need to focus on? Where and when might you try to apply them to your teaching?"",""question_id"":137,""requirements"":{""answer_type"":""text"",""text_required"":true}},""response"":{""free_text"":{""id"":""22063_ft"",""text"":""Holding high ...""}}}]")"
json_map = read.csv(text = json.csv) %>%
as_tibble()
json_map %>%
mutate(response = map(response, ~ fromJSON(.x) %>%
unnest(everything()) %>%
unnest(everything(), names_sep = "_") %>%
mutate(across(everything(), as.character)))) %>%
unnest(response) %>%
type_convert()
# A tibble: 5 × 9
X object_name id text question_id requirements_answer_…¹ requirements_text_re…² free_text_id
<int> <chr> <dbl> <chr> <dbl> <chr> <lgl> <chr>
1 1 B3 reflection 46445 What did you se… 1 text TRUE 46445_ft
2 1 B3 reflection 46446 What do you fee… 2 text TRUE 46446_ft
3 2 B1 Reflection 22061 What did you se… 135 text TRUE 22061_ft
4 2 B1 Reflection 22062 What do you fee… 136 text TRUE 22062_ft
5 2 B1 Reflection 22063 Which of the ‘k… 137 text TRUE 22063_ft
# ℹ abbreviated names: ¹requirements_answer_type, ²requirements_text_required
# ℹ 1 more variable: free_text_text <chr>
Reproduceble data from this:
dput(json_map)
structure(list(X = 1:2, object_name = c("B3 reflection", "B1 Reflection"
), response = c("[{\"question\":{\"id\":46445,\"text\":\"What did you see in this module that you already do or have seen in other classrooms?\",\"question_id\":1,\"requirements\":{\"answer_type\":\"text\",\"text_required\":true}},\"response\":{\"free_text\":{\"id\":\"46445_ft\",\"text\":1}}},{\"question\":{\"id\":46446,\"text\":\"What do you feel is the gap between your current practice and what you have seen in this module?\",\"question_id\":2,\"requirements\":{\"answer_type\":\"text\",\"text_required\":true}},\"response\":{\"free_text\":{\"id\":\"46446_ft\",\"text\":1}}}]",
"[{\"question\":{\"id\":22061,\"text\":\"What did you see in this module that you already do or have seen in other classrooms?\",\"question_id\":135,\"requirements\":{\"answer_type\":\"text\",\"text_required\":true}},\"response\":{\"free_text\":{\"id\":\"22061_ft\",\"text\":\"Strong classroom routines and high expectations\"}}},{\"question\":{\"id\":22062,\"text\":\"What do you feel is the gap between your current practice and what you have seen in this module?\",\"question_id\":136,\"requirements\":{\"answer_type\":\"text\",\"text_required\":true}},\"response\":{\"free_text\":{\"id\":\"22062_ft\",\"text\":\"Making sure I am consistent in classroom routines\"}}},{\"question\":{\"id\":22063,\"text\":\"Which of the \\u2018key takeaways\\u2019 do you need to focus on? Where and when might you try to apply them to your teaching?\",\"question_id\":137,\"requirements\":{\"answer_type\":\"text\",\"text_required\":true}},\"response\":{\"free_text\":{\"id\":\"22063_ft\",\"text\":\"Holding high ...\"}}}]"
)), class = c("tbl_df", "tbl", "data.frame"), row.names = c(NA,
-2L))