I have a character column (not list, it’s from a sql dataset) that has the structure of “List((query, count))” with differing amounts of entires. I’d like to transform this into a dataframe:
library(tibble) #> Warning: package 'tibble' was built under R version 3.4.3 original_df <- tribble( ~query, ~id, "List((slime, 2))", 1L, "List((gift, 3), (bear, 1))", 2L, "List((sugar, 10))", 3L, "List((fluff, 2), (pie, 5), (plum, 8), (dark, 10), (long, 2))", 4L ) desired_df <- tribble( ~query, ~count, ~id, "slime", 2L, 1L, "gift", 3L, 2L, "bear", 1L, 2L, "sugar", 10L, 3L, "fluff", 2L, 4L, "pie", 5L, 4L, "plum", 8L, 4L, "dark", 10L, 4L, "long", 2L, 4L )
I’m struggling splitting the column and extracting the relevant pairings (query and count); if I could get it to a wide format (e.g. id, query1, count1, query2, count2, etc. with many of them having null entries for the later ones), I can convert it to the long tidy dataframe. Ideally solution will be fairly performant, as I will have to do it over tens of thousands of rows, some of which might have dozens of entries.