1

I have table with three columns. The first column contains sample names, the second numbers and the third IDs. Here is an example:

Samples Count   ID
S1      99057   Lactobacillales
S1      608     Enterobacterales
S1      507     Choanoflagellida
S1      495     Burkholderiales
S1      456     Pseudomonadales
S1      398     Nostocales
S1      286     Suessiales
S2      3742    Lactobacillales
S2      631     Pseudomonadales
S2      276     Micrococcales
S2      261     Burkholderiales
S2      244     Choanoflagellida
S2      239     Rhizobiales
S2      239     Enterobacterales

I need to transpose and consolidate the table so that the first column contains the IDs, the second the number for each ID in sample S1, and the third the number for each ID in sample S2. Example:

ID                  S1      S2
Lactobacillales     3742    99057
Enterobacterales    239     608
Choanoflagellida    244     507
Burkholderiales     261     495
Pseudomonadales     631     456
Nostocales          0       398
Suessiales          0       286
Micrococcales       276     0
Rhizobiales         239     0

So far I do it in Excel, but that's not feasible for larger tables. How can this be done in R? Any help greatly appreciated.

user2952422
  • 45
  • 4
  • 1
    `reshape(df, timevar = "Samples", v.names = "Count", idvar = "ID", direction = "wide")`. See [demo](https://rextester.com/KRLIT96887) – Parfait Apr 12 '19 at 17:56

1 Answers1

1

You can use dcast function from reshape2 package- 

> reshape2::dcast(dt,ID~Samples,value.var = "Count",fill=0)

                ID    S1   S2
1  Burkholderiales   495  261
2 Choanoflagellida   507  244
3 Enterobacterales   608  239
4  Lactobacillales 99057 3742
5    Micrococcales     0  276
6       Nostocales   398    0
7  Pseudomonadales   456  631
8      Rhizobiales     0  239
9       Suessiales   286    0

Note- If your data is large than use dcast.data.table from data.table package.

Rushabh Patel
  • 2,672
  • 13
  • 34