I have a large data frame with about 3.5 million rows and 44 columns. Some of the columns are character class and the others are list. The elements in the list columns do not always share equal length. Below is a representative example.
| name | id | scores | hobbies |
|---|---|---|---|
| A | A102 | 22 | c(sports,music) |
| B | B098 | c(20,71,2) | c(sports,dancing,music) |
| C | B876 | c(66,76) | c(running,dancing) |
I would like to unnest this table into the following table:
| name | id | scores | hobbies |
|---|---|---|---|
| A | A102 | 22 | sports |
| A | A102 | 22 | music |
| B | B098 | 20 | sports |
| B | B098 | 71 | dancing |
| B | B098 | 2 | music |
| C | B876 | 66 | running |
| C | B876 | 76 | dancing |
I have tried the tidyr:unnest but it takes forever to execute. I have also tried dt_hoist from the tidyfast package, but it requires that the columns to unnest must all be the same length when unnested, so it won't work on my dataset. Additionally, I have tried the data table solution posted on R bloggers (see below). However, it requires that each list column is unnested separately, and moreover it doesn't retain other list columns in the data table.
dt[, list(scores = as.character(unlist(scores))), by = list(name, id)]
Anyone here has any clues as to how to unnest this efficiently? Any help would be greatly appreciated. Thanks in advance.