how to merge tibble in a tibble row wise

user_14154924 0
Viewed 117

I have a tibble, each row of it is another tibble, see below:

what_i_have <- tibble::tibble(
    chr = c("chr1", "chr2"),
    s1 = list(
        tibble::tibble(pos = c(1:3), c1_v = c(1:3)),
        tibble::tibble(pos = c(10:15), c2_v = c(10:15))
    ),
    s2 = list(
        tibble::tibble(pos = c(2:4), c1_v = c(1:3)),
        tibble::tibble(pos = c(13:16), c2_v = c(3:6))
    ),
    s3 = list(
        NULL, 
        tibble::tibble(pos = c(12:15), c2_v = c(2:5))
    )
)

it looks like this

# A tibble: 2 x 4
  chr   s1               s2               s3              
  <chr> <list>           <list>           <list>          
1 chr1  <tibble [3 × 2]> <tibble [3 × 2]> <NULL>          
2 chr2  <tibble [6 × 2]> <tibble [4 × 2]> <tibble [4 × 2]>

I want to create a column, sT, which merges tibble row wise (except chr) by pos, see below

what_i_want <- tibble::tibble(
    chr = c("chr1", "chr2"),
    s1 = list(
        tibble::tibble(pos = c(1:3), c1_v = c(1:3)),
        tibble::tibble(pos = c(10:15), c2_v = c(10:15))
    ),
    s2 = list(
        tibble::tibble(pos = c(2:4), c1_v = c(1:3)),
        tibble::tibble(pos = c(13:16), c2_v = c(3:6))
    ),
    s3 = list(
        NULL, 
        tibble::tibble(pos = c(12:15), c2_v = c(2:5))
    ),
    sT  = list(
        tibble::tibble(pos = c(1:3), s1 = c(1:3)) |> 
            dplyr::full_join(tibble::tibble(pos = c(2:4), s2 = c(1:3)), by = "pos") |>
            dplyr::full_join(tibble::tibble(pos = c(NA), s3 = c(NA)), by = "pos"),

        tibble::tibble(pos = c(10:15), s1 = c(10:15)) |> 
            dplyr::full_join(tibble::tibble(pos = c(13:16), s2 = c(3:6)), by = "pos") |> 
            dplyr::full_join(tibble::tibble(pos = c(12:15), s3 = c(2:5)), by = "pos")
    )
)

It looks like this

# A tibble: 2 x 5
  chr   s1               s2               s3               sT               
  <chr> <list>           <list>           <list>           <list>          
1 chr1  <tibble [3 × 2]> <tibble [3 × 2]> <NULL>           <tibble [4 × 3]>
2 chr2  <tibble [6 × 2]> <tibble [4 × 2]> <tibble [4 × 2]> <tibble [7 × 4]>

in particular, the sT tibble looks like the following, note that the column name is the merged column names (s1, s2, s3) not the original (c1_v, c2_v)

[[1]]
# A tibble: 5 x 4
    pos    s1    s2 s3   
  <int> <int> <int> <lgl>
1     1     1    NA NA   
2     2     2     1 NA   
3     3     3     2 NA   
4     4    NA     3 NA   
5    NA    NA    NA NA   

[[2]]
# A tibble: 7 x 4
    pos    s1    s2    s3
  <int> <int> <int> <int>
1    10    10    NA    NA
2    11    11    NA    NA
3    12    12    NA     2
4    13    13     3     3
5    14    14     4     4
6    15    15     5     5
7    16    NA     6    NA

How to get what_i_want from what_i_have? Thank you.

tibble dplyr r merge
1 Answers

You can transpose s columns and use reduce with full_join

library(tidyverse)

df$sT <- transpose(df[,-1]) %>% 
  map(~discard(.x, is.null)) %>% 
  map(~imap(.x, ~setNames(.x, c("pos", .y)))) %>% 
  map(~reduce(.x, full_join, by = "pos"))

Note that result does not give you what you wrote. First list in result has 4 rows not 5 and does not have s3 column. Reason is NULL in s3. To get those results you should replace NULL in s3 with tibble(pos = NA, c1_v = NA).

user_121484020
Related