How can I use R to change my table

Question

Using R how would I change my table from this:

GeneID    GeneName    Species    Paralogues    Domains    Total
 1234      FGF1        Human         4            2         6
 5678      FGF1        Mouse         2            1         3
 9104      FGF1       Chicken        3            0         3

To a table that represents the total column e.g.

GeneName    Human    Mouse    Chicken
  FGF1        6        3         3

@Sathish This does not reproduce OPs expected output; have you tried this yourself? — Maurits Evers, May 02 '18 at 12:30
@MauritsEvers May be this `dcast(df, GeneName ~ Species, value.var = 'Total')` — Sathish, May 02 '18 at 16:37

Maurits Evers · Accepted Answer · 2018-05-02T12:44:02.970

2

You can use dplyr::spread to reshape from long to wide:

library(tidyverse);
df %>% 
    select(GeneName, Species, Total) %>% 
    spread(Species, Total)
#  GeneName Chicken Human Mouse
#1     FGF1       3     6     3

Sample data

df <- read.table(text =
    "GeneID    GeneName    Species    Paralogues    Domains    Total
 1234      FGF1        Human         4            2         6
 5678      FGF1        Mouse         2            1         3
 9104      FGF1       Chicken        3            0         3", header  = T)

edited May 02 '18 at 12:44

answered May 02 '18 at 12:35

Maurits Evers

49,617
4
47
68

There is no need for group_by. – zx8754 May 02 '18 at 12:41
@zx8754 Good point & thanks. Fixed. – Maurits Evers May 02 '18 at 12:44

score 0 · Answer 2 · answered May 02 '18 at 12:39

You can use dcast() from data.table.

For the input:

text <- 
    "GeneID    GeneName    Species    Paralogues    Domains    Total
     1234      FGF1        Human         4            2         6
     5678      FGF1        Mouse         2            1         3
     9104      FGF1       Chicken        3            0         3"

my_data <- read.table(text = text, header = TRUE)

You can transform your data with GeneName as LHS and Species as RHS with value.var Total:

data.table::dcast(my_data,
                  GeneName ~ Species, 
                  value.var = "Total")

The result:

  GeneName Chicken Human Mouse
1     FGF1       3     6     3

How can I use R to change my table

2 Answers2

Sample data