R: Extract columns from list of data.frames in a tibble

Question

I am wondering how to manipulate a list containing data.frames stored in a tibble.

Specifically, I would like to extract two columns from a data.frame that are stored in a tibble list column.

I would like to go from this tibble c

random_data<-list(a=letters[1:10],b=LETTERS[1:10])
x<-as.data.frame(random_data, stringsAsFactors=FALSE)
y<-list()
y[[1]]<-x[1,,drop=FALSE]
y[[3]]<-x[2,,drop=FALSE]
c<-tibble(z=c(1,2,3),my_data=y)

to this tibble d

d<-tibble(z=c(1,2,3),a=c('a',NA,'b'),b=c('A',NA,'B'))

thanks

Iain

Columns `a` and `b` are factors in dataset `c`, but they are characters in dataset `d`. Is this intentional? — wibeasley, Jul 07 '17 at 20:38
if they are, that wasn't intentional, they should be characters in both — Iain, Jul 07 '17 at 20:48

score 11 · Answer 1 · answered Jul 07 '17 at 20:28

11

c2 is the final output.

library(tidyverse)

c2 <- c %>%
  filter(!map_lgl(my_data, is.null)) %>%
  unnest() %>%
  right_join(c, by = "z") %>%
  select(-my_data)

answered Jul 07 '17 at 20:28

www

38,575
12
48
84

agreed - very nice solution – Iain Jul 07 '17 at 21:10

score 6 · Answer 2 · edited Jul 07 '17 at 20:43

6

I think this does the trick with d the requested tibble:

library(dplyr)

new.y <- lapply(y, function(x) if(is.null(x)) data.frame(a = NA, b = NA) else x)
d <- cbind(z = c(1, 2, 3), bind_rows(new.y)) %>% tbl_df()


# # A tibble: 3 x 3
#     z      a      b
#  <dbl> <fctr> <fctr>
# 1   1      a      A
# 2   2     NA     NA
# 3   3      b      B

edited Jul 07 '17 at 20:43

M--

25,431
8
61
93

answered Jul 07 '17 at 20:29

Constantinos

1,327
7
17

Rich Scriven · Accepted Answer · 2017-07-07T20:56:46.740

6

You could create a function f to change out the NULL values, then apply it to the my_data column and finish with unnest.

library(dplyr); library(tidyr)

unnest(mutate(c, my_data = lapply(my_data, f)))
# # A tibble: 3 x 3
#       z     a     b
#   <dbl> <chr> <chr>
# 1     1     a     A
# 2     2  <NA>  <NA>
# 3     3     b     B

Where f is a helper function to change out the NULL values, and is defined as

f <- function(x) {
    if(is.null(x)) data.frame(a = NA, b = NA) else x
}

edited Jul 07 '17 at 20:56

answered Jul 07 '17 at 20:34

Rich Scriven

97,041
11
181
245

Showing the helper function was very useful as it helped me understand the issue that I was having, namely that I need to account for empty entries in my list column. – Iain Jul 07 '17 at 21:09

score 5 · Answer 4 · answered Jul 07 '17 at 20:36

Do you know your column names ahead of time?

extract_column <- function( d, column_name ) {
  if( is.null(d) ) {
    NA_character_
  } else {
    as.character(d[[column_name]])
  }  
}


cc %>% 
  dplyr::mutate(
    a = purrr::map_chr(.$my_data, extract_column, column_name="a"),
    b = purrr::map_chr(.$my_data, extract_column, column_name="b")
  ) %>% 
  dplyr::select(-my_data)

(I renamed your c tibble to cc so it can't collide with c().)

unfortunately, I don't know always know the name of the columns — Iain, Jul 07 '17 at 21:10

R: Extract columns from list of data.frames in a tibble

4 Answers4