turning json type column into R dataframe

Question

Here I have a dataframe df1 that I would like to turn into a dataframe df2. Does anybody have any suggestions/ideas?

df1 <- data.frame (ID  = c("UniqueValue1", "UniqueValue2", "UniqueValue3",
                          "UniqueValue4", "UniqueValue5", "UniqueValue6"),
                  stringtext = c("Factor1:1.0, Factor2:2.0, Factor3:3.0"))

into...

df2 <- data.frame (ID  = c("UniqueValue1", "UniqueValue2", "UniqueValue3",
                          "UniqueValue4", "UniqueValue5", "UniqueValue6"),
                  Factor1 = c("1.0", "1.0", "1.0", "1.0", "1.0", "1.0"),
                  Factor2 = c("2.0", "2.0", "2.0", "2.0", "2.0", "2.0"),
                  Factor3 = c("3.0", "3.0", "3.0", "3.0", "3.0", "3.0"))

Is there a way where you don't have to manually specify 'Factor1', 'Factor2', and 'Factor3'? Like if whatever is behind the colon, turn that into a column, and what ever is AFTER the colon, turn that into its specific value. If we can get that, that would be awesome. Any help or feedback regarding this would be highly appreciated! Thanks!

dswdsyd · Accepted Answer · 2021-04-14T15:22:46.280

0

You can try something like this, it may not be very elegant but gets the outcome you want.

column_names <- as.data.frame(unlist(str_split(df1$stringtext,","))) %>%
setNames(c("factor")) %>%
distinct() %>%
mutate(factor = trimws(factor)) %>%
mutate(factor = gsub(":.+$","",factor))

df1 <- df1 %>%
tidyr::separate(stringtext, column_names$factor ,sep=",")

df <- as.data.frame((sapply(df1,function(x) gsub("^F.+:","",trimws(x)))))

edited Apr 14 '21 at 15:22

answered Apr 14 '21 at 14:47

dswdsyd

576
4
12

Thanks! Is there a way where you don't have to manually specify 'Factor1', 'Factor2', and 'Factor3'? Like if whatever is behind the colon, turn that into a column, and what ever is AFTER the colon, turn that into its specific value. If we can get that, that would be awesome. Thanks so much for your help. Much appreciated! – puj831 Apr 14 '21 at 14:54

score 0 · Answer 2 · answered Apr 14 '21 at 15:34

If you have json string in a column then you can try one thing -
map_dfr is from purrr package and fromJSON is from jsonlite.

df <- map_dfr(df$ColumnWithJsonString, fromJSON)

This will convert the string to multiple columns.

Let me know if that's helpful
Thanks!

turning json type column into R dataframe

2 Answers2