Manipulating a data frame to have all results in one row

Question

Suppose I have a data table that looks like this:

options(stringsAsFactors = F)
letters <- list("A", "B", "C", "D")
numbers <- list(2, 1, 1, 2)
value <- list(.44, .54, .21, .102)
test <- list(.001,.0005, .0004, .01)

DF <- data.frame(cbind(numbers, value, test))
row.names(DF) <- letters


> # DF
  #     numbers   value    test
  # A       2     0.44     0.001
  # B       1     0.54     5e-04
  # C       1     0.21     4e-04
  # D       2     0.102    0.01

I would like the structure of the data frame to look like this:

DF

A.numbers   A.value   A.test   B.numbers   B.value.   B.test   C.numbers   C.value   C.test   D.numbers   D.value   D.test
2           0.44      0.001    1           0.54       5e-04    1           0.21      4e-04    2           0.102     0.01

I think I need to use cbind or rbind but I'm unsure how! Any direction would be greatly appreciated!

Daniel Anderson · Answer 1 · 2017-05-22T19:06:11.533

3

Here's a tidyverse solution

library(tidyverse)
DF %>% 
    rownames_to_column("letters") %>%  
    gather(var, val, -letters) %>% 
    unite(let_var, c(letters, var), sep = ".") %>% 
    spread(let_var, val)

A.numbers A.test A.value B.numbers B.test B.value C.numbers C.test C.value D.numbers D.test D.value
1         2  0.001    0.44         1  5e-04    0.54         1  4e-04    0.21         2   0.01   0.102

edited May 22 '17 at 19:06

answered May 22 '17 at 19:00

Daniel Anderson

2,394
13
26

1

I was just about to post a very similar solution. Only note here, you could use `rownames_to_column()` instead of hard coding letters. – Sotos May 22 '17 at 19:03

Lamia · Answer 2 · 2017-05-22T20:19:33.163

1

Using base R, you could do:

d = unlist(apply(DF,1,function(x) x))
d = data.frame(t(d))

This returns:

A.numbers   A.value    A.test B.numbers   B.value    B.test C.numbers   C.value    C.test D.numbers 
   2.0000    0.4400    0.0010    1.0000    0.5400    0.0005    1.0000    0.2100    0.0004    2.0000 
  D.value    D.test 
   0.1020    0.0100

edited May 22 '17 at 20:19

answered May 22 '17 at 19:57

Lamia

3,845
1
12
19

Nice solution! I think it's worth pointing out the `c` doesn't combine anything really. An equivalent would be to just use the rows themselves with `function(x) x` – Mike H. May 22 '17 at 20:08
@MikeH. You're right, for clarity, it's probably better to replace it by identity function. I've edited the answer. – Lamia May 22 '17 at 20:18

score 0 · Answer 3 · answered May 22 '17 at 19:29

Also, data.table's dcast supports casting multiple variables so you could try:

DF$names <- row.names(DF)
res <- data.table::dcast(setDT(DF), .~names, value.var = setdiff(names(DF), "names"), sep = ".")[, .:= NULL]

res
#   numbers.A numbers.B numbers.C numbers.D value.A value.B value.C value.D test.A test.B test.C test.D
#1:         2         1         1         2    0.44    0.54    0.21   0.102  0.001  5e-04  4e-04   0.01

Manipulating a data frame to have all results in one row

3 Answers3