I'm looking for some assistance with dealing with a dataframe which contains a JSON column (I hope I am describing it accurately as such, I'm fairly new to dealing with data of this kind). I have some data I have collected from a pilot experiment. One part asks participants several different questions about the experiment. All the questions and responses have then been saved in one single column in the dataframe called "response". The structure for one row is as follows:
{"Moral":"Should contribute money","behaviour":"I wanted to win", "Understanding":"5","Age":"62", "Gender":"Female", "Nationality":"White"}
What I would like, is to be able to split these into individual columns so that each question and response is placed in its own column. In other words, get 6 different columns for each of these responses.
I hope that this makes sense and is clear? Any help for this would be greatly appreciated. Here is a link to the data in question, which I imagine may make things clearer.
Unfortunately, this doesn't seem to have solved my issue. Using fromJSON on specifically the "response" column provided this error
Error: parse error: trailing garbage
I tried this on a different dataframe, one which contains a column with only a single key value pair {Prestige:0} and it did not produce any error, however fromJSON simply converted this to a dataframe with a single column which maintained this format {Prestige:0}.
If you had any further suggestions, I would greatly appreciate it. For example, perhaps I was mistaken and this is not JSON data at all.
Thank you so much for your reply and solution, that works perfectly for the response column. However I am still having trouble with the remainder of the data. The approach you provided does not work for my other property columns. Here is a reprex for the node data attempting to split property2 (which contains only {Prestige:0} or {Prestige:1}). Alsoproperty5 which is perhaps even more awkward in that the data is in the format:
"{"prestige_list": [5, 5, 8, 7, 5, 6], ....
My desired output in this case would be 6 columns labelled round_ 1 -> round_6 for each of the contents of prestige list, conform list and payoff list (so 18 columns total). I can then use the other functions from tidyverse to tidy this into long format data.
In any case, both functions fail on the third line providing the error:
Error in if (is.character(txt) && length(txt) == 1 && nchar(txt, type = "bytes") < : missing value where TRUE/FALSE needed
Thank you so much! I feel a bit stupid with it being that simple. I've had to add a little extra code to select the columns I need to keep (running drop_na() to just drop all incomplete cases loses the useful data, ironically keeping the bits I don't need). But with a little extra wrangling, I've now been able to extract exactly what I need, so once again thank you so much for your help! I really apprecite it.