CODEWITHSUNDEEP
rtibble
David Mallard
David Mallard

Reputation: 31

Convert list with nested lists to one-row tibble with nested dataframe

I'm trying to convert an object retrieved via an API and converted to a list by jsonlite into a single-row tibble (ie, each item in the list becomes a variable in the tibble).

The complication is that aside from a set of individual values, the final item nested in the object is an array of objects, which jsonlite converts to a list of two lists, each containing two items (I used simplifyDataFrame = FALSE).

A simplified structure resembling the actual API data is used in the reprex below.

library(tidyverse)

dat <- list(
  id = 1,
  name = "Jo Bloggs",
  offices = list(
    list(office_id = 999, title = "Vice President"),
    list(office_id = 998, title = "Director of Operations")
  )
)

df <- dat %>% as_tibble()
df
#> # A tibble: 2 x 3
#>      id name      offices   
#>   <dbl> <chr>     <list>    
#> 1  1.00 Jo Bloggs <list [2]>
#> 2  1.00 Jo Bloggs <list [2]>

Created on 2018-02-26 by the reprex package (v0.2.0).

Converting using as_tibble() produces a two-row data frame duplicating all of the non-nested values, with the two nested lists each on a separate row.

What should I change so that instead of this, I end up with a single row of data and the final column contains a nested 2 x 2 dataframe/tibble? Thanks in advance.

Upvotes: 3

Views: 1806

Answers (1)

Tony416
Tony416

Reputation: 624

You can use mutate to change the offices column. 

res1 <- df %>% mutate(offices = list(bind_rows(map(offices,as.tibble))))
> res1
# A tibble: 2 x 3
     id name      offices         
  <dbl> <chr>     <list>          
1     1 Jo Bloggs <tibble [2 x 2]>
2     1 Jo Bloggs <tibble [2 x 2]>

Here is how the mutate process:

  1. First apply as.tibble to each element of the offices.
  2. bind_rows all individual tibble
  3. Finally, make sure the offices column is a list type.

You will find the two 2x2 tibbles are same. So we just need to remove the duplicated rows (Here I just take the id column as unique key). 

res <- res1[!duplicated(res1[1]),]
> res
# A tibble: 1 x 3
     id name      offices         
  <dbl> <chr>     <list>          
1     1 Jo Bloggs <tibble [2 x 2]>

Upvotes: 2

Related Questions