This is a test version of Biostars. For the public version, visit https://www.biostars.org.
how to de-aggregate a table in R

Dear all,

i have this table. does someone know how to de-aggregate it ?

plant   tissue  count
tomato  leaf    1
tomato  root    4
tomato  shoot   5
solanus leaf    3
solanus root    2
solanus shoot   4

what i want is a dataframe like that: 

    leaf    root    shoot
tomato  1   4   5
solanus 3   2   4

thanks in advance for you tips

r
• 2,668 views • link
written by lessismore lessismore

I add another option to solve this with tidyr:

1. reproduce the same table

test <- structure(list(plant = c("tomato", "tomato", "tomato", "solanus", 
    "solanus", "solanus"), tissue = c("leaf", "root", "shoot", "leaf", 
    "root", "shoot"), count = c(1L, 4L, 5L, 3L, 2L, 4L)), class = "data.frame", row.names = c(NA, 
    -6L))

2. make it wider

library(tidyverse)
test %>% pivot_wider(names_from = tissue, values_from = count)
• 0 views • link
written by lessismore lessismore

3 answers

Buenas tardes amiga/o,

You can use dcast():

df
    plant tissue count
1  tomato   leaf     1
2  tomato   root     4
3  tomato  shoot     5
4 solanus   leaf     3
5 solanus   root     2
6 solanus  shoot     4

require(data.table)

dcast(data = df, formula = plant ~ tissue, value.var = 'count')
    plant leaf root shoot
1 solanus    3    2     4
2  tomato    1    4     5

Kevin

• 1 views • link
written by Kevin Blighe Kevin Blighe

Base R:

> xtabs(count ~ ., data = test)
         tissue
plant     leaf root shoot
  solanus    3    2     4
  tomato     1    4     5

> test
    plant tissue count
1  tomato   leaf     1
2  tomato   root     4
3  tomato  shoot     5
4 solanus   leaf     3
5 solanus   root     2
6 solanus  shoot     4

out of R, with datamash:

$ datamash  -sH crosstab 1,2 unique 3  <test.txt 

GroupBy(plant)  GroupBy(tissue) unique(count)
    leaf    root    shoot
solanus 3   2   4
tomato  1   4   5
• 1 views • link
written by cpad0112 cpad0112

Great work as usual, cpad. Enjoy your weekend.

• 0 views • link
written by Kevin Blighe Kevin Blighe

Tidy way 

library(tidyverse)
data <- tibble(plant = c("tomato","tomato","tomato","solanus" ,"solanus","solanus") , tissue = c("leaf" ,"root","shoot","leaf","root","shoot") , count = c(1,4,5,3,2,4))

> data
# A tibble: 6 x 3
  plant   tissue count
  <chr>   <chr>  <dbl>
1 tomato  leaf       1
2 tomato  root       4
3 tomato  shoot      5
4 solanus leaf       3
5 solanus root       2
6 solanus shoot      4

data %>% spread(key = tissue ,  value = count) 

# A tibble: 2 x 4
  plant    leaf  root shoot
  <chr>   <dbl> <dbl> <dbl>
1 solanus     3     2     4
2 tomato      1     4     5
• 1 views • link
written by Chirag Parsania Chirag Parsania

Log in to answer this question.

More posts like this