-1

I am new to both StackOverflow and R stats, so please bear with me. I have a lot of experience with coding in SAS, but am trying to learn R. I commonly use SAS and R to transform large datasets, I have a species by study site matrix as follows:

Species Status Role Site1 Site2 Site3...Site25
A_a      S      P     0     0     0       1
A_b      SO     X     1     25    0       0
B_a      S      P     0      2    1       1
B_b      S      X     0      1    0       0 ...

I would like to transform this table and create 2 new variables called "Site" and "Count" based on the site variable names and the count data within each site:

Species Status Role Site Count
A_a      S      P   Site1  0
A_a      S      P   Site2  0
A_a      S      P   Site3  0
A_a      S      P   Site25 1
A_b      SO     X   Site1  1
A_b      SO     X   Site2  25
A_b      SO     X   Site3  0
A_b      SO     X   Site25 0 ...
B_b      S      X   Site25 0

I think that this may be beyond the simple t() function, and have looked into the packages reshape and reshape2, but am kind of lost as to how to proceed. Would anyone have had a situation like this and could lend a hand with coding? Thanks, JimH

Jim H
  • 13
  • 4
  • Use `reshape2::melt`. – Roland Jan 05 '16 at 21:18
  • 1
    Did you try `melt(data, c("Species", "Status", "Role"), value.name="Count")`? – Pierre L Jan 05 '16 at 21:19
  • It would be great if you could supply a minimal reproducible example to go along with your question. Something we can work from and use to show you how it might be possible to answer your question. That way others can also befit form your question, and the accompanying answer, in the future. You can have a look at [this SO post](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) on how to make a great reproducible example in R. – Eric Fail Jan 05 '16 at 21:27
  • Also, since you come from SAS you might find [this _Migrating to R for SAS/SPSS/Stata Users_ PDF](http://scc.stat.ucla.edu/page_attachments/0000/0115/09F_migrating.pdf) from UCLA helpful. Cheers! – Eric Fail Jan 05 '16 at 21:30

2 Answers2

1

You can use dplyr/tidyr to do like this:

install.packages(c("tidyr", "dplyr"), dependencies = TRUE)
library(dplyr)
library(tidyr)
df %>% gather(Site, Count, grep('Site', names(df))) %>% arrange(Species)
Eric Fail
  • 8,191
  • 8
  • 72
  • 128
Gopala
  • 10,363
  • 7
  • 45
  • 77
1

Or a bit old-school in base R (I realize the code could be more concise, please feel free to optimize),

df <- structure(list(Species = structure(1:4, .Label = c("A_a", "A_b", 
"B_a", "B_b"), class = "factor"), Status = structure(c(1L, 2L, 
1L, 1L), .Label = c("S", "SO"), class = "factor"), Role = structure(c(1L, 
2L, 1L, 2L), .Label = c("P", "X"), class = "factor"), Site1 = c(0L, 
1L, 0L, 0L), Site2 = c(0L, 25L, 2L, 1L), Site3 = c(0L, 0L, 1L, 
0L)), .Names = c("Species", "Status", "Role", "Site1", "Site2", 
"Site3"), class = "data.frame", row.names = c(NA, -4L))
df
#>   Species Status Role Site1 Site2 Site3
#> 1     A_a      S    P     0     0     0
#> 2     A_b     SO    X     1    25     0
#> 3     B_a      S    P     0     2     1
#> 4     B_b      S    X     0     1     0

 reshape(df, 
   varying = c("Site1", "Site2", "Site3"), 
   v.names = "Count",
   timevar = "Site", 
   times = c("Site1", "Site2", "Site3"), 
   new.row.names = 1:1000,
   direction = "long")
#>   Species Status Role  Site Count id
#> 1      A_a      S    P Site1     0  1
#> 2      A_b     SO    X Site1     1  2
#> 3      B_a      S    P Site1     0  3
#> 4      B_b      S    X Site1     0  4
#> 5      A_a      S    P Site2     0  1
#> 6      A_b     SO    X Site2    25  2
#> 7      B_a      S    P Site2     2  3
#> 8      B_b      S    X Site2     1  4
#> 9      A_a      S    P Site3     0  1
#> 10     A_b     SO    X Site3     0  2
#> 11     B_a      S    P Site3     1  3
#> 12     B_b      S    X Site3     0  4
Eric Fail
  • 8,191
  • 8
  • 72
  • 128
  • Hi all, thanks for getting back to me with your suggestions and advice. I will try these out on my larger dataset. I also downloaded the pdf of migrating from SAS to R, very informative! – Jim H Jan 06 '16 at 17:13
  • @JimH, did this solve your problem? If so we should close the question. – Eric Fail Jan 14 '16 at 18:15