CODEWITHSUNDEEP
rdplyrtidyr
amk
amk

Reputation: 359

Tidying data: from cell containing vector to column

I have a tibble that contains vectors of doubles for motifs (e.g motif_1 and motif_2):

table <- tibble(ID = c(1,2,3,4,5),
                motif_1 = list(c(runif(n = 5, min = 0, max = 10)), c(runif(n = 5, min = 0, max = 10)), c(runif(n = 5, min = 0, max = 10)), c(runif(n = 5, min = 0, max = 10)), c(runif(n = 5, min = 0, max = 10))),
                motif_2 = list(c(runif(n = 5, min = 0, max = 10)), c(runif(n = 5, min = 0, max = 10)), c(runif(n = 5, min = 0, max = 10)), c(runif(n = 5, min = 0, max = 10)), c(runif(n = 5, min = 0, max = 10))))

# A tibble: 5 x 3
     ID motif_1   motif_2  
  <dbl> <list>    <list>   
1     1 <dbl [5]> <dbl [5]>
2     2 <dbl [5]> <dbl [5]>
3     3 <dbl [5]> <dbl [5]>
4     4 <dbl [5]> <dbl [5]>
5     5 <dbl [5]> <dbl [5]>

What I would like to do is creating columns in the number regarding the length of vectors (always 5, so P1...P5) to which would be assigned values of the vectors correspond to the particular position in the vector.

What in overall would lead to this output:

# A tibble: 10 x 7
      ID motif      P1    P2    P3    P4    P5
   <dbl> <chr>   <dbl> <dbl> <dbl> <dbl> <dbl>
 1     1 motif_1  2.61  4.19 0.182  4.26 5.60 
 2     2 motif_1  5.88  5.24 5.97   3.26 0.390
 3     3 motif_1  6.86  1.25 9.98   4.56 2.93 
 4     4 motif_1  3.02  8.99 7.33   8.12 4.18 
 5     5 motif_1  2.40  6.61 6.35   8.42 0.202
 6     1 motif_2  2.39  7.12 7.61   3.83 0.506
 7     2 motif_2  5.20  3.09 5.53   1.52 6.05 
 8     3 motif_2  3.97  2.90 5.94   6.85 2.99 
 9     4 motif_2  8.37  5.35 3.84   1.88 0.358
10     5 motif_2  6.53  2.78 8.59   1.57 4.90

To be more specific, while considering only 1st row of the input table:

FROM:
# A tibble: 5 x 3
     ID motif_1                           motif_2  
  <dbl> <list>                            <list>   
1     1 c(2.61, 4.19, 0.182, 4.26, 5.60)  <dbl [5]>

TO:
    ID   motif    P1    P2    P3    P4    P5
   <dbl> <chr>   <dbl> <dbl> <dbl> <dbl> <dbl>
 1     1 motif_1  2.61  4.19 0.182  4.26 5.60 
 2     1 motif_2  ...

Thank you in advance for any suggestions.

Upvotes: 0

Views: 46

Answers (1)

Ronak Shah
Ronak Shah

Reputation: 388962

Here's a tidyverse approach -

library(dplyr)
library(tidyr)

table %>%
  unnest(cols = starts_with('motif')) %>%
  pivot_longer(cols = -ID) %>%
  group_by(ID, name) %>%
  mutate(col = paste0('P', row_number())) %>%
  ungroup %>%
  pivot_wider(names_from = col, values_from = value)

#    ID name       P1    P2    P3    P4      P5
#   <dbl> <chr>   <dbl> <dbl> <dbl> <dbl>   <dbl>
# 1     1 motif_1  1.52 1.39  2.33   4.66 2.66   
# 2     1 motif_2  4.40 7.54  6.29   7.10 0.00625
# 3     2 motif_1  8.58 0.458 4.42   7.99 1.22   
# 4     2 motif_2  4.75 2.20  3.80   6.13 3.52   
# 5     3 motif_1  5.61 2.07  1.28   7.53 8.95   
# 6     3 motif_2  1.11 2.44  6.68   4.18 7.88   
# 7     4 motif_1  3.74 6.65  0.948  3.84 2.74   
# 8     4 motif_2  1.03 4.35  9.85   8.93 8.86   
# 9     5 motif_1  8.15 4.49  8.10   8.12 7.94   
#10     5 motif_2  1.75 1.31  6.53   3.44 6.57

Upvotes: 1

Related Questions