How to flatten a dataframe in this way in R?

Question

Suppose I have this data frame df:

> df <- data.frame(a=c(1,2))
> df$b <- list(list(),list(id=c('d', 'e')))
> df
  a    b
1 1 NULL
2 2 d, e
> df$b
[[1]]
list()

[[2]]
[[2]]$id
[1] "d" "e"

How do I get the following data frame:

> df2 <- data.frame(a=c(1,2,2),b=c(NA,'d','e'))
> df2
  a    b
1 1 <NA>
2 2    d
3 2    e

In other words, there should be a new row for every list item in column b.

I looked at these three things:

jsonlite::flatten(df)
rlang::flatten(df)
purrr::flatten(df)

and also these two questions, and nothing seemed quite right.

Yes, I'm getting this data from an API that returns json.

score 1 · Accepted Answer · answered Mar 30 '20 at 21:12

1

We can use unnest with the keep_empty as TRUE

library(dplyr)
library(tidyr)
df %>%
    unnest(c(b), keep_empty = TRUE) %>% 
    unnest(c(b), keep_empty = TRUE)
# A tibble: 3 x 2
#      a b    
#  <dbl> <chr>
#1     1 <NA> 
#2     2 d    
#3     2 e

answered Mar 30 '20 at 21:12

akrun

874,273
37
540
662

Great! Why do you need two `unnest` calls? I ran just the one, and it looked like it did nothing. – dfrankow Mar 30 '20 at 21:47
@dfrankow because you have nested `list()` inside and it won't recursively flatten – akrun Mar 30 '20 at 21:49
1

I see, each `unnest` removes a `list`. You're my hero. – dfrankow Mar 30 '20 at 21:51

How to flatten a dataframe in this way in R?

1 Answers1

Linked