Unnest dataframe whose values in rows have mixed type values-CodePudding

I am working with a dataframe in R using tidyverse. I need to unnest it because it contains rows stored as dataframes. My dataframe ex is next (included as dput):

ex
# A tibble: 2 x 2
  name1 values      
  <chr> <list>      
1 A     <df [3 x 2]>
2 B     <df [4 x 2]>

It is actually a tibble. In order to unnest I use next code:

library(tidyverse)
#Code
ex2 <- ex %>%  
  tidyr::unnest(c(values), .drop = TRUE)

Which returns next error:

Error:
! Can't combine `..1$value` <character> and `..2$value` <integer>.

The issue is that some columns in the nested dataframe are character and other integer. So I believed using as.character() would help but it did not worked.

How can I unnest this tibble? Many thanks!

Data is next:

#Data
ex <- structure(list(name1 = c("A", "B"), values = list(structure(list(
    value = c("Home", "Draw", "Away"), odd = c("1.58", "3.75", 
    "6.50")), class = "data.frame", row.names = c(NA, 3L)), structure(list(
    value = c(2L, 3L, 1L, 0L), odd = c("7.77", "29.34", "2.80", 
    "1.92")), class = "data.frame", row.names = c(NA, 4L)))), row.names = c(NA, 
-2L), class = c("tbl_df", "tbl", "data.frame"))

Many thanks!

CodePudding user response：

The main trick to obtain the desired output is to cycle over each row of the tibble to transform the first column of each nested list into character. Probably, there's a shorter way to do it, but here's a solution using the tidyverse.

library(tidyverse)

ex2 <- ex |> 
  # Mutate across values column, map over each row
  mutate(across(values, ~ map(1:nrow(ex), ~ ex |> 
                                # Select values column
                                select(values) |>
                                # Get lists out of the tibble
                                magrittr::extract2(1) |> 
                                # Get .x entry of the lists
                                magrittr::extract2(.x) |> 
                                # mutate across value to turn it into character
                                mutate(across(value, as.character))))) %>%
  unnest(c(values))

## A tibble: 7 × 3
#  name1 value odd  
#  <chr> <chr> <chr>
#1 A     Home  1.58 
#2 A     Draw  3.75 
#3 A     Away  6.50 
#4 B     2     7.77 
#5 B     3     29.34
#6 B     1     2.80 
#7 B     0     1.92

CodePudding user response：

Another option - where you lapply over the values list and modify the columns to be character. Then you can unnest it.

library(dplyr)
library(tidyr)
library(purrr)

ex$values = lapply(ex$values, function(v) {
  modify(v, as.character)
})
  
ex %>%
  unnest(values)

# A tibble: 7 × 3
  name1 value odd  
  <chr> <chr> <chr>
1 A     Home  1.58 
2 A     Draw  3.75 
3 A     Away  6.50 
4 B     2     7.77 
5 B     3     29.34
6 B     1     2.80 
7 B     0     1.92

CodePudding user response：

First splitting by group is one option

library(dplyr)
library(tidyr)

group_split(ex, name1)[[1]] %>% 
  unnest(values) %>% 
  bind_rows(group_split(ex, name1)[[2]] %>% 
  unnest(values) %>% 
  mutate(value = as.character(value)))
# A tibble: 7 × 3
  name1 value odd
  <chr> <chr> <chr>
1 A     Home  1.58
2 A     Draw  3.75
3 A     Away  6.50
4 B     2     7.77
5 B     3     29.34
6 B     1     2.80
7 B     0     1.92

With base R

do.call(rbind, lapply(seq_along(ex$name1), function(x) 
  cbind(name1 = ex$name1[x], ex$values[[x]])))
  name1 value   odd
1     A  Home  1.58
2     A  Draw  3.75
3     A  Away  6.50
4     B     2  7.77
5     B     3 29.34
6     B     1  2.80
7     B     0  1.92