Reputation: 1064

How split column of list-values into multiple columns?

I have the following situation, the column power_dbm0 has values as list. All elements are list of length 11.

# A tibble: 10 x 2
   real_pat power_dbm0
   <chr>    <list>    
 1 am       <dbl [11]>
 2 fax      <dbl [11]>
 3 fp       <dbl [11]>
 4 fpw      <dbl [11]>

I'd like to know how could split theses values as each order is a new column. Preferably, i'd like a dplyr-like solution. I have tried some solutions with unnest or separate function from tidyr, but it didn't success.

Thanks in advance,

Follow the data:

structure(list(real_pat = c("am", "fax", "fp", "fpw"), power_dbm0 = list(
    structure(c(0.0142857142857143, 0.0742857142857143, 0.111428571428571, 
    0.138571428571429, 0.208571428571429, 0.278571428571429, 
    0.368571428571429, 0.508571428571429, 0.648571428571429, 
    0.771428571428571, 0.871428571428571), .Names = c("0%", "10%", 
    "20%", "30%", "40%", "50%", "60%", "70%", "80%", "90%", "100%"
    )), structure(c(0.342857142857143, 0.342857142857143, 0.342857142857143, 
    0.342857142857143, 0.342857142857143, 0.342857142857143, 
    0.342857142857143, 0.342857142857143, 0.342857142857143, 
    0.342857142857143, 0.342857142857143), .Names = c("0%", "10%", 
    "20%", "30%", "40%", "50%", "60%", "70%", "80%", "90%", "100%"
    )), structure(c(0.0142857142857143, 0.622857142857143, 0.808571428571429, 
    0.851428571428571, 0.857142857142857, 0.871428571428571, 
    0.874285714285714, 0.885714285714286, 0.894285714285714, 
    0.911428571428571, 0.914285714285714), .Names = c("0%", "10%", 
    "20%", "30%", "40%", "50%", "60%", "70%", "80%", "90%", "100%"
    )), structure(c(0.514285714285714, 0.514285714285714, 0.514285714285714, 
    0.514285714285714, 0.514285714285714, 0.514285714285714, 
    0.514285714285714, 0.514285714285714, 0.514285714285714, 
    0.514285714285714, 0.514285714285714), .Names = c("0%", "10%", 
    "20%", "30%", "40%", "50%", "60%", "70%", "80%", "90%", "100%"
    )))), .Names = c("real_pat", "power_dbm0"), row.names = c(NA, 
-4L), class = c("tbl_df", "tbl", "data.frame"))

Upvotes: 3

Answers (3)

G. Grothendieck

Reputation: 270448

1) This is a one-line base solution:

with(dd, do.call("rbind", setNames(power_dbm0, real_pat)))

giving:

            0%        10%       20%       30%       40%       50%       60%
am  0.01428571 0.07428571 0.1114286 0.1385714 0.2085714 0.2785714 0.3685714
fax 0.34285714 0.34285714 0.3428571 0.3428571 0.3428571 0.3428571 0.3428571
fp  0.01428571 0.62285714 0.8085714 0.8514286 0.8571429 0.8714286 0.8742857
fpw 0.51428571 0.51428571 0.5142857 0.5142857 0.5142857 0.5142857 0.5142857
          70%       80%       90%      100%
am  0.5085714 0.6485714 0.7714286 0.8714286
fax 0.3428571 0.3428571 0.3428571 0.3428571
fp  0.8857143 0.8942857 0.9114286 0.9142857
fpw 0.5142857 0.5142857 0.5142857 0.5142857

2) or to get the real_pat as a column rather than names:

with(dd, data.frame(real_pat, do.call("rbind", power_dbm0), check.names = FALSE))

giving:

  real_pat         0%        10%       20%       30%       40%       50%
1       am 0.01428571 0.07428571 0.1114286 0.1385714 0.2085714 0.2785714
2      fax 0.34285714 0.34285714 0.3428571 0.3428571 0.3428571 0.3428571
3       fp 0.01428571 0.62285714 0.8085714 0.8514286 0.8571429 0.8714286
4      fpw 0.51428571 0.51428571 0.5142857 0.5142857 0.5142857 0.5142857
        60%       70%       80%       90%      100%
1 0.3685714 0.5085714 0.6485714 0.7714286 0.8714286
2 0.3428571 0.3428571 0.3428571 0.3428571 0.3428571
3 0.8742857 0.8857143 0.8942857 0.9114286 0.9142857
4 0.5142857 0.5142857 0.5142857 0.5142857 0.5142857

3) Using dplyr we could write this:

library(dplyr)
dd %>% { bind_cols(select(., real_pat), bind_rows(!!!.$power_dbm0)) }

giving:

# A tibble: 4 x 12
  real_pat   `0%`  `10%` `20%` `30%` `40%` `50%` `60%` `70%` `80%` `90%` `100%`
  <chr>     <dbl>  <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>  <dbl>
1 am       0.0143 0.0743 0.111 0.139 0.209 0.279 0.369 0.509 0.649 0.771  0.871
2 fax      0.343  0.343  0.343 0.343 0.343 0.343 0.343 0.343 0.343 0.343  0.343
3 fp       0.0143 0.623  0.809 0.851 0.857 0.871 0.874 0.886 0.894 0.911  0.914
4 fpw      0.514  0.514  0.514 0.514 0.514 0.514 0.514 0.514 0.514 0.514  0.514

3a) or using the .id= argument of bind_rows and the magrittr %$% :

library(dplyr)
library(magrittr)

dd %$%
   setNames(power_dbm0, real_pat) %$%
   bind_rows(!!!., .id = "real_pat")

giving:

# A tibble: 4 x 12
  real_pat   `0%`  `10%` `20%` `30%` `40%` `50%` `60%` `70%` `80%` `90%` `100%`
  <chr>     <dbl>  <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>  <dbl>
1 am       0.0143 0.0743 0.111 0.139 0.209 0.279 0.369 0.509 0.649 0.771  0.871
2 fax      0.343  0.343  0.343 0.343 0.343 0.343 0.343 0.343 0.343 0.343  0.343
3 fp       0.0143 0.623  0.809 0.851 0.857 0.871 0.874 0.886 0.894 0.911  0.914
4 fpw      0.514  0.514  0.514 0.514 0.514 0.514 0.514 0.514 0.514 0.514  0.514

3b) or without %$% :

library(dplyr)

dd %>%
   { setNames(.$power_dbm0, .$real_pat) } %>%
   { bind_rows(!!!., .id = "real_pat") }

giving:

# A tibble: 4 x 12
  real_pat   `0%`  `10%` `20%` `30%` `40%` `50%` `60%` `70%` `80%` `90%` `100%`
  <chr>     <dbl>  <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>  <dbl>
1 am       0.0143 0.0743 0.111 0.139 0.209 0.279 0.369 0.509 0.649 0.771  0.871
2 fax      0.343  0.343  0.343 0.343 0.343 0.343 0.343 0.343 0.343 0.343  0.343
3 fp       0.0143 0.623  0.809 0.851 0.857 0.871 0.874 0.886 0.894 0.911  0.914
4 fpw      0.514  0.514  0.514 0.514 0.514 0.514 0.514 0.514 0.514 0.514  0.514

Upvotes: 8

MKR

Reputation: 20095

An option can be as:

cbind(df[1],t(sapply(df$power_dbm0,function(x)x)))

# real_pat         0%        10%       20%       30%       40%       50%       60%       70%       80%       90%      100%
# 1       am 0.01428571 0.07428571 0.1114286 0.1385714 0.2085714 0.2785714 0.3685714 0.5085714 0.6485714 0.7714286 0.8714286
# 2      fax 0.34285714 0.34285714 0.3428571 0.3428571 0.3428571 0.3428571 0.3428571 0.3428571 0.3428571 0.3428571 0.3428571
# 3       fp 0.01428571 0.62285714 0.8085714 0.8514286 0.8571429 0.8714286 0.8742857 0.8857143 0.8942857 0.9114286 0.9142857
# 4      fpw 0.51428571 0.51428571 0.5142857 0.5142857 0.5142857 0.5142857 0.5142857 0.5142857 0.5142857 0.5142857 0.5142857

Additional option using simplify2array (based on feedback from @@G.Grothendieck):

cbind(df[1],t(simplify2array(df$power_dbm0)))

Upvotes: 2

akrun

Reputation: 887991

1) We could transpose the 'power_dbm0' column, unlist the nested list and then bind with the first column

library(tidyverse)
df1 %>%
   pull(power_dbm0) %>%
   transpose %>%
   map_df(unlist) %>% 
   bind_cols(df1[1], .)
# A tibble: 4 x 12
#   real_pat   `0%`  `10%` `20%` `30%` `40%` `50%` `60%` `70%` `80%` `90%` `100%`
#  <chr>     <dbl>  <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>  <dbl>
#1 am       0.0143 0.0743 0.111 0.139 0.209 0.279 0.369 0.509 0.649 0.771  0.871
#2 fax      0.343  0.343  0.343 0.343 0.343 0.343 0.343 0.343 0.343 0.343  0.343
#3 fp       0.0143 0.623  0.809 0.851 0.857 0.871 0.874 0.886 0.894 0.911  0.914
#4 fpw      0.514  0.514  0.514 0.514 0.514 0.514 0.514 0.514 0.514 0.514  0.514

2) Or another option would be to melt and then do a spread. Here, we also include unnest as the OP mentioned in the post

library(tidyverse)
library(reshape2)
df1 %>% 
    mutate(power_dbm0 = map(power_dbm0, ~melt(.x) %>% 
                          rownames_to_column('rn') %>%
                          mutate(rn = factor(rn, levels = rn)))) %>% 
    unnest %>% 
    spread(rn, value)
# A tibble: 4 x 12
#  real_pat   `0%`  `10%` `20%` `30%` `40%` `50%` `60%` `70%` `80%` `90%` `100%`
#  <chr>     <dbl>  <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>  <dbl>
#1 am       0.0143 0.0743 0.111 0.139 0.209 0.279 0.369 0.509 0.649 0.771  0.871
#2 fax      0.343  0.343  0.343 0.343 0.343 0.343 0.343 0.343 0.343 0.343  0.343
#3 fp       0.0143 0.623  0.809 0.851 0.857 0.871 0.874 0.886 0.894 0.911  0.914
#4 fpw      0.514  0.514  0.514 0.514 0.514 0.514 0.514 0.514 0.514 0.514  0.514

3) Or with pmap and spread

df1 %>%
     pmap_df(~ tibble(real_pat = ..1, nm = names(..2), val = ..2))  %>%
     spread(nm, val)

NOTE: All the solutions used dplyr and related packages in the tidyverse

4) Or we could unlist the 'power_dbm0', create a matrix as they are all equal length and then bind with the first column (base R) - The column names can be changed if needed

data.frame(df1[1], matrix(unlist(df1$power_dbm0), ncol = 11, byrow = TRUE))

Upvotes: 3

How split column of list-values into multiple columns?

Answers (3)

Related Questions